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