How to Find the Last Row Using VBA in Excel – 4 Reliable Methods

When working with Excel VBA, getting the last row with data is a common task, especially when writing macros to automate operations.

This article explains 4 practical methods to get the last row using VBA.

Each approach has its own ideal use case depending on the structure of your data.

How to get the number of the last row in a table using VBA

This is a commonly used method that works even if there are blanks in the column. It starts from the bottom of the worksheet and moves upward to the last non-empty cell.

In that case, first write it as follows:

Dim Last_Row As Long
Last_Row = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
MsgBox "The last row is:" & vbCrLf & "Row " & Last_Row & "."

When I run this code

" is displayed.

Then just use the "Last_Row" variable for your processing.

How to specify a range from the first row to the last row in VBA

If you want to copy data from the top row to another sheet or workbook, you could copy the data starting from the first row, but this can take quite a bit of time as the number of rows increases.

In such cases, you can complete the process in one step by selecting the entire data and copying and pasting it.

Here is one way to select from the first line of data to the end of the data in that case.

If you know the first cell of your data is B3,

Dim Last_Row As Long
Last_Row = Cells(Rows.Count, 2).End(xlUp).Row
Range(Cells(3, 2), Cells(Last_Row, 6)).Select

This allows you to select the entire range of data.

Searching from top to bottom using VBA

It is also possible to process the data from top to bottom and end the processing when the value of a specific column becomes XX.

Dim i As Long    'Declare a variable i
i = 3    Assign 3 to 'i
Do
    Cells(i, 2).Interior.ColorIndex = 6    'Shade the i-th row, column B, yellow
    i = i + 1    'Add 1 to i and move it down one line
Loop Until Cells(i, 2).Value = ""    'If row i's column B is blank, end the process.

However, in this case, if any intermediate data has been deleted or if there is data that has not been entered, the process will stop there and will not be completed.

It would be fine if an error occurred at that point, but if no error occurs and the data below is completely ignored, it is difficult to notice, which can lead to mistakes or omissions in work.

Therefore, on this page we recommend searching from the bottom up rather than from the top down.

The specific methods are introduced below.

How to select the last cell in a row using VBA

Let's say you want to select column B of the last row. In that case, write it like this:

Cells(ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row, 2).Select

When I run this code

The cell B14 is now selected.

If you want to enter data in the row after the last row, you need to select one row below. You can also select one row below after this, but in that case

Cells(ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row + 1, 2).Select

By doing so...

..., and cell B15 can be selected.