How to Find Last Row Using Excel VBA (5 Easy ways)

There are 4 columns in the dataset representing sales information. These columns are Sales Person, Region, Product, and Price. We’ll find the last row in the dataset.

How to Find the Last Row Using Excel VBA: 5 Suitable Ways

Method 1 – Using SpecialCells to Find Last Row

Open the Developer tab and select Visual Basic.

A new window of Microsoft Visual Basic for Applications will pop up. From Insert, select Module.

A Module will be opened. Use the following code in the Module.

Sub LastRow_SpecialCells()
Dim LastRow As Long
LastRow = Range("A:A").SpecialCells(xlCellTypeLastCell).Row
MsgBox LastRow
End Sub

We’ve created a sub-procedure called LastRow_SpecialCells, where a Long type of variable LastRow has been declared. We defined the variable using the Range.SpecialCells method. We’ve used column A (A:A) as the range and provided xlCellTypeLastCell as the type parameter of SpecialCells, which will return the last cell for the range (for this case, from column A). We’ve used a message box to show the result.

Save the code and go back to the worksheet.
Open the View tab.
From Macros, select the View Macros option.

A dialog box will pop up.
Select the LastRow_SpecialCells macro and select the current workbook within Macros in.
Run the selected Macro.

You will get a message box showing the last row number.

Method 2 – Using Rows.Count for Non-Empty Cells

Open a Module like in Method 1.
Use the following code in the Module.

Sub LastRow_NonEmpty()
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox LastRow
End Sub

CELLS(Rows.Count, 1) will count how many rows are there in the first column. End(xlUp).Row will find the last used row in an Excel range. We’ve used a message box to show the result.

Save the code and go back to the worksheet.
Open View Macros.
Select the LastRow_NonEmpty macro and choose the current workbook forMacros in.
Run the selected Macro.

You will get a message box showing the last row number.

Method 3 – Using Rows.Count for Any Selected Column

Use the following code in the Module.

Sub LastRow_AnyColumn()
Dim LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
MsgBox LastRow
End Sub

In the Range we put column B is a parameter. Rows.Count will count how many rows are there in column B. End(xlup).Row will find the last used row in an Excel range. We put the result in a message box.

In the Macro dialog box, select LastRow_AnyColumn and select the current workbook for Macros in.
Run the selected Macro.

You’ll get a message box showing the last row number.

Method 4 – Using UsedRange to Find the Last Row

Create a Module box and use the following code in the Module.

Sub LastRow_UsedRange()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
MsgBox "Last Row: " & LastRow
End Sub

ActiveSheet.UsedRange.Rows.Count as the parameter of ActiveSheet.UsedRange.Rows will return the last row.

In the Macro dialog box, select LastRow_UsedRange.
Run the selected Macro.

You’ll get a message box showing the last row number.

Method 5 – Find Last Row Using the Range.Find Method

Use the following code in the Module.

Sub Range_Find_Method()
Dim LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
MsgBox "Last Row: " & LastRow
End Sub

In the Cells.Find method, we declared 7 parameters. In the What parameter, (“*”) will find the first non-empty cell. The SearchOrder:=xlByRows parameter will move right-to-left and loop up through each row until it finds a non-empty cell.

In the Macro dialog box, select Range_Find_Method.
Choose the destination sheet.
Run the selected Macro.

You’ll get a message box showing the last row number.

Download the Practice Workbook

Macro to Find Last Row.xlsm