Find the Last Row with Data in a Range Using Excel VBA Macros

This is the sample dataset.

Opening the VBA Editor

Press Alt+F11.
Select Insert > Module.

Method 1 – Use of the Range.End Property to Find the Last Row with Data in a Range Using VBA

Steps

Open the VBA Editor.
Enter the following code:

Sub range_end_method()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet
LastRow = Range("B4").End(xlDown).Row
MsgBox "Last used row: " & LastRow

End Sub

Save the file.
Press Alt+F8 to open the Macro dialog box.
Select range_end_method.

Click Run.

This is the output.

Method 2 – Using the Range.Find Property of VBA in Excel

Cells.Find(“*”, searchorder:=xlByRows, searchdirection:=xlPrevious)

What:=”*” – The asterisk is a wildcard character that finds text or numbers in the cell.

SearchOrder:=xlByRows – goes through each row before moving on to the next. The direction is left-to-right or right-to-left depending on the SearchDirection argument. The additional option here is xlByColumns, locates the last column.

SearchDirection:=xlPrevious – determines the direction. xlPrevious searches from right-to-left or bottom-to-top. The opposite is xlNext.

Steps

Open the VBA Editor.
Enter the following code:

Sub range_find_method()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet
LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
MsgBox "Last used row: " & LastRow

End Sub

Save the file.
Press Alt+F8 to open the Macro dialog box.
Select range_end_method.

Click Run.

This is the output.

Method 3 – Using the SpecialCells Function to Find the Last Row Using VBA

Steps

Open the VBA Editor.
Enter the following code:

Sub specialcells_method()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet
LastRow = sht.Cells.SpecialCells(xlCellTypeLastCell).Row
MsgBox "Last used row: " & LastRow

End Sub

Save the file.
Press Alt+F8 to open the Macro dialog box.
Select specialcells_method.

Click Run.

This is the output.

Method 4 – Using the UsedRange Function to Find the Last Row with Data in a Range

Steps

Open the VBA Editor.
Enter the following code:

Sub usedRange_method()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
MsgBox "Last used row: " & LastRow

End Sub

Save the file.
Press Alt+F8 to open the Macro dialog box.
Select usedRange_method.

Click Run.

This is the output.

Method 5 – Using Table Range using VBA in Excel

Steps

Open the VBA Editor.
Enter the following code:

Sub TableRange_method()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet
LastRow = sht.ListObjects("Table1").Range.Rows.Count + 3
MsgBox "Last used row: " & LastRow

End Sub

Note: 3 was added to the last row as the dataset starts after row 3.

Save the file.
Press Alt+F8 to open the Macro dialog box.
Select TableRange_method.

Click Run.

This is the output.

Method 6 – Using a Named Range to Find the Last Row with Data in a Range

Steps

Open the VBA Editor.
Enter the following code:

Sub nameRange_method()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet
LastRow = sht.Range("Named_Range").Rows.Count + 1
MsgBox "last used row: " & LastRow

End Sub

Note: 1 is added to the LastRow because the range starts after row 1.

Save the file.
Press Alt+F8 to open the Macro dialog box.
Select nameRange_method.

Click Run.

This is the output.

Method 7 – Using the CurrentRegion Function of VBA in Excel

Steps

Open the VBA Editor.
Enter the following code:

Sub CurrentRegion_method()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet
LastRow = sht.Range("B4").CurrentRegion.Rows.Count + 3 'add numbers according to your choice'
MsgBox "Last used row: " & LastRow

End Sub

Note: The range should be the first cell of your dataset. Add your row number. Here, 3 because the dataset stars after row 3.