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

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

Use this method to find the last row with data in a given range.

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.

Read More: Excel VBA to Find Multiple Values in Range

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

Use the Range.Find method:

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

This method works by pressing Ctrl+End to go to the last row. To find the last row with data:

 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

If your dataset has a named range, you can use this code.

 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.

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

• Click Run.

This is the output.

 Things to Remember

• The Range.End only works on a single row or column.

Download Practice Workbook

Download the practice workbook.

Related Articles

• How to Find Blank Cells Using VBA in Excel
• How to Find Exact Match Using VBA in Excel
• Excel VBA to Find Value in Column
• Excel VBA: Find the Next Empty Cell in Range
• FindNext Using VBA in Excel
• Excel VBA to Find Matching Value in Column