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

In Excel VBA, you can use different methods to find the last row. In this article, I am going to explain different methods of to find last row in Excel VBA. To make this explanation more understandable, see the below overview. There are 4 columns in the dataset representing sales information. These columns are Sales Person, Region, Product, and Price.

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

In this article, we will use VBA SpecialCells, Row.Count, Used.Range, Range.Find methods to get the last row in our dataset.

1. Using SpecialCells to Find Last Row

You can use the SpecialCells method to find the last row using VBA.

Initially, open the Developer tab >> then select Visual Basic.

A new window of Microsoft Visual Basic for Applications will pop up. Now, from Insert >> select Module option.

Subsequently, a Module will be opened. Then write the following code in the Module.

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

Here, I’ve created a sub-procedure called LastRow_SpecialCells, where a Long type of variable LastRow has been declared.
Then defined the variable using the Range.SpecialCells method. Here, I’ve used column A (A:A) as the range. Provided xlCellTypeLastCell as the type parameter of SpecialCells, this will return the last cell for the range (for this case, from column A). I’ve used a message box to show the result.
After that, Save the code and go back to the worksheet. Again, open the View tab >> from Macros >> select View Macros option.

Then, a dialog box will pop up.
Now, from the Macro name select the LastRow_SpecialCells also select the workbook within Macros in.
Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

2. Using Rows.Count for Non-Empty Cells

You can use the Rows.Count method to find the last row using Excel VBA.

Firstly, open a module box like method 1.
After creating the box, write 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

Here, I’ve created a sub-procedure called LastRow_NonEmpty, where a Long type of variable LastRow has been declared.
Now, CELLS(Rows.Count, 1) will count how many rows are there in the first column. Then used End(xlUp).Row now, this will find the last used row in an Excel range.
In the end, I’ve used a message box to show the result.
Like the previous method, Save the code and go back to the worksheet. Here, again open the View tab >> from Macros >> select View Macros like the previous method.
Now, a dialog box will pop up. Then, from the Macro name select the LastRow_NonEmpty also select the workbook within Macros in.
Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

3. Using Rows.Count for Any Selected Column

By using any selected column in VBA, you can find the last row.

To begin with, write 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

Here, I’ve created a sub-procedure called LastRow_AnyColumn, where a Long type of variable LastRow has been declared.
Then, in the Range given column B is a parameter and also Rows.Count, this will count how many rows are there in a given column B. Next, used End(xlup). Row which will find the last used row in an Excel range.
Last, I’ve used a message box to show the result.
Next, in the Macro dialog box, select the LastRow_AnyColumn also select the workbook within Macros in.
Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

4. Using UsedRange to Find Last Row

You can use the UsedRange property of the worksheet to find the last row using VBA.

Create a Module box and write the following code in the Module as shown in example 1.

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

Here, I’ve created a sub-procedure called LastRow_UsedRange, where a Long type of variable LastRow has been declared.
Next, define the variable using the ActiveSheet.UsedRange.Rows method also provided ActiveSheet.UsedRange.Rows.Count as the parameter of ActiveSheet.UsedRange.Rows, this will return the last row.
I’ve used a message box to show the result.
Further, in the Macro dialog box, select the LastRow_UsedRange also select the workbook within Macros in.
Finally, Run the selected Macro.
Thus, it will pop up a message box showing the last row number.

5. Find Last Row Using Range.Find Method

You can use the Range.Find method to find the last row using VBA.

First, write 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

Here, I’ve created a sub-procedure called Range_Find_Method, where a Long type of variable LastRow has been declared.
Then, defined the variable using the Cells.Find method. Here, declared 7 parameters. In What parameter I used (“*”) which will find the first non-empty cell. SearchOrder:=xlByRows parameter will move right-to-left and also loops up through each row until it finds a non-empty cell.
Now, in the Macro dialog box, select the Range_Find_Method also select the worksheet within Macros in.
Finally, Run the selected Macro.

Thus, it will pop up a message box showing the last row number.

Download Practice Workbook

Macro to Find Last Row.xlsm

Conclusion

In this article, I’ve explained 5 ways to VBA find the last row in Excel. In case you have any confusion or question regarding these methods you may comment down below.