One way is to: (Assumes index column begins at A1)
MsgBox Range("A1").End(xlDown).Row
Which is looking for the 1st unoccupied cell downwards from A1 and showing you its ordinal row number.
You can select the next empty cell with:
Range("A1").End(xlDown).Offset(1, 0).Select
If you need the end of a dataset (including blanks), try: Range("A:A").SpecialCells(xlLastCell).Row
If you want to find the last populated cell in a particular column, the best method is:
Range("A" & Rows.Count).End(xlUp).Row
This code uses the very last cell in the entire column (65536 for Excel 2003, 1048576 in later versions), and then find the first populated cell above it. This has the ability to ignore "breaks" in your data and find the true last row.
You can also use
Cells.CurrentRegion
to give you a range representing the bounds of your data on the current active sheet
Msdn says on the topic
Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only.
Then you can determine the column count via
Cells.CurrentRegion.Columns.Count
and the row count via
Cells.CurrentRegion.Rows.Count
You may also use:
UsedRange.Rows.Count
To find the last filled column use the following :
lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Source: Stackoverflow.com