VBA Tip: Determining the Number of the Last Row

You often need to insert data at the end of a table in an Excel worksheet, and to do this, you need to know the number of the first empty row available in the table.

The number of the last non-empty row in a table is obtained using:

Cells(Rows.Count, COLUMN).End(xlUp).Row

First empty cell in column A

To find out the last non-empty cell in a column, use the code above and replace "COLUMN" with 1:

Sub example()

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    MsgBox lastRow

End Sub

This gives you the number of the last non-empty row in column A:

excel vba last non empty row

To obtain the number of the first empty row, simply add 1 to the result:

Sub example()

    lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    MsgBox lastRow

End Sub

This now gives you the number of the first empty row in column A:

excel vba first empty row last
To summarize how this works, the search begins at the last row in the worksheet (Rows.Count) then works upwards (xlUp) until it finds a non-empty cell.

First non-empty cell using the column letter

If you have a column letter rather than a column number, use Range in place of Cells:

Sub example()

    lastRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    
    MsgBox lastRow

End Sub
excel vba first empty row last