Find the Last Used Row in Excel with VBA

A common VBA task is finding the last used row in a worksheet. This is especially useful when the amount of data changes and the macro needs to work with a dynamic range.

Find the last used row in a column

The following example finds the last used row in column A:

Dim lastRow As Long

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

The result is the row number of the last non-empty cell in column A.

How it works

Rows.Count returns the number of rows in the worksheet. The code starts from the bottom of column A and then moves upward with .End(xlUp) until it finds a non-empty cell.

Finally, .Row returns the row number.

Use a specific worksheet

In most real applications it is better to explicitly specify the worksheet:

Dim ws As Worksheet
Dim lastRow As Long

Set ws = ThisWorkbook.Worksheets("Data")

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

This avoids relying on whichever worksheet happens to be active when the macro runs.

Find the last used row in another column

The second argument of Cells determines the column. For example, column E is column number 5:

lastRow = ws.Cells(ws.Rows.Count, 5).End(xlUp).Row

Examples

Column	Column number
A	1
B	2
E	5
J	10

Using the last row in a range

Once the last row is known, it can be used to create a dynamic range:

Dim ws As Worksheet
Dim lastRow As Long
Dim dataRange As Range

Set ws = ThisWorkbook.Worksheets("Data")

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

Set dataRange = ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 5))

In this example the range starts from cell A2 and extends to column E on the last used row.

Important note

This method finds the last used row in the column you specify. If column A contains blanks while other columns continue further down, column A will not tell you the last used row of the entire dataset.

Choose a column that is reliably populated for every data row.