Add rows below last row

Question

I'm designing a data submission spreadsheet with 12 rows of data (currently row 22 to 33). Rows 34 to 42 are filled with text. I'd like to add a new row (row 34) below row 33 if more than 12 data sets are to be entered, with the same format as row 33, using a macro assigned to a button. That's easy, but if I need another new row (now row 35) below row 34, I need a code is flexible to do that; mine always adds the new line below row 33, not below the last row of the data entry set. How do I do this? I'm quite new to VBA...

Thanks,

Andy

Answer 1

lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Change "A" to the name of a column that always has data in it.

your lastrow variable can then be used as:

Range("A" & lastrow).Value = "blah"

Answer 2

I don't think Don's answer will work, because you state you have text in rows 34 to 42.

If the text value of the cell in row 34 doesn't change, you can search for that cell and insert a row above it.

Dim someText As String
someText = "Text value of row 34"

Dim currentRange As Range
Set currentRange = Selection

With Range("A:A").find(someText) 'change the range if needed
    .EntireRow.Insert
    .Offset(-2).EntireRow.Copy
    .Offset(-1).EntireRow.PasteSpecial xlPasteFormats 'change the paste type as needed
End With
Application.CutCopyMode = False 'get rid of the marching ants
currentRange.Select 'select whatever was selected before
'alternative: Range("A1").Select