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

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

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 to 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

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 then move up. When it meets cells that are not empty, it gets the row number and displays it in the MsgBox.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 2,542