VBA find last row

Here, I’m going to show you a few ways to get the last row using the VBA code.

Find the last row in the current column

This is the example we are going to use.

Click any cell inside column A (A1:A6) and run this code.

Sub LastRow()
    MsgBox Selection.End(xlDown).Row
End Sub

You can also set a cell, instead of using the active cell.

Sub LastRow()
    MsgBox Range("A1").End(xlDown).Row
End Sub

Both Subs will return the same result in this case.

Find the last row in the current column (with blanks)

The Subs I presented you will work until there is no blank cell on the way. If there is at least one, it will return incorrect results.

Let’s modify our Sub a bit, to deal with this problem.

Sub LastRowWithBlanks()
    row_number = Range("A1048576").End(xlUp).Row
    MsgBox row_number
End Sub

Code explanation

Now, instead of moving the cursor down from the first cell, we move the cursor to the last row and the move up. When it meets cells that is not empty, it gets the row number and displays it in the MsgBox.