UPDATE June 13, 2008:

Another alternative way to find the last row with data :

Function LastRow() As Long

    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    LastRow = ix

End Function

I did a simple test, and the function will return the correct last row position even when the data was filtered

Read also the opposite version of this function, instead of row, in here we find the last column containing data.

UPDATE Oct 25, 2008:

As you can see there are several way to find the last row that contain data in Excel, which you can found in the post comments below, please feel free to read it.

Today, when I trying to solve another problem of mine, I found more simple approach to find the last raw.

As you probably know, Excel has a special cell like blank cell, cell containing formula, cell with comments etc, let me quote it from Excel help.

xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

So, I think why not we use Excel special cell type xlCellTypeLastCell to find the row position of that LastCell? Isn’t the row position of last cell in the used range will be the same with the last row that contain data in Excel?

I then create a dummy sheet, fill it with data, and run this simple vba code, and yes, the vba code return the right position of last row with data in corresponding worksheet.

'
Cells.SpecialCells(xlCellTypeLastCell).Row
'

But unfortunately, again, this vba code won’t work on filtered Excel Worksheet, the code will return position of the last row which is not filtered.

Please try it, and leave me a comment.

ORIGINAL:

This one is my fav, I commonly used this simple Excel VBA function to get the last row on active worksheet that contain any kind of data.

The function will return the position of the last row, so we can assume that after that row, there are no other data on the active worksheet.

Public Function GetLastRowWithData() As Long

    Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long

    Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
    lLastDataRow = ExcelLastCell.Row
    lRow = ExcelLastCell.Row
    '
    Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    '
    lLastDataRow = lRow

    GetLastRowWithData = lLastDataRow

End Function

We can call this formula from the cell formula bar and return the position of the last row with data, or we can also call it from others function or procedure (excel macro).

The most common use of this formula is when I need to paste some data to the next row after the last row that contain any data on my Excel sheet, and this formula really help to find the right location to paste it.

FIN.