Determining next row in Excel

[richardbarrett]

I don't have much experience with Excel macros and I need to write something rather simple.

How do I determine what the 'next' row is in the active worksheet? Say rows 1 - 12 have been filled with data, how can I determine that row 13 is the next blank row?

Thanks,

[RetiredGeek]

Richard,

This will do the trick assuming your data is filling from the top of the sheet (row 1). If the data has embedded blank lines these will be ignored with this method.

Option Explicit

Sub NextBlank()

   Dim lLastRow As Long
   
   lLastRow = Application.Rows.Count  '*** Gets No. rows in your version of Excel ***
   
   Cells(lLastRow, 1).Select
   Selection.End(xlUp).Offset(1, 0).Select
   MsgBox "The Next Blank Row is:" & ActiveCell.Row, _
          vbOKOnly + vbInformation, "What's Next?"
End Sub

[rory]

Just as an FYI, that does assume that column A is populated for all rows of data. There's also no need to select anything:

msgbox cells(rows.count, "a").end(xlup).row + 1

[RetiredGeek]

Rory,

I like the no selection part!
However, I fail to see how your solution does away with the need to assume one column has no missing data?

[rory]

It doesn't - they were two separate comments. (Sorry, I should have been clearer)Since we don't know if missing data is an issue, I didn't address that - you would usually use Find for that.

[richardbarrett]

Thanks to both of you.