Find Last Row with Data in a Range Using Excel VBA Macros (7 Methods)

While working with Microsoft Excel, finding the last rows or columns is a common task. We use the keyboard to find the last used rows or columns. But, you may find yourself in a situation where you need to find the last used row from a complex dataset. In this tutorial, you will learn to find the last row with data in a range using VBA in Excel with practical examples and proper illustrations.

How to Find Last Row with Data in a Range Using Excel VBA Macros: 7 Methods

In the upcoming sections, we are going to provide you with seven methods that will help you to find the last row with data in a range using VBA in Excel. We recommend you learn and apply all these methods to enrich your Excel knowledge.

To demonstrate this tutorial, we are going to use this dataset:

Here, we have a dataset consisting of some players’ information. We will use this to teach you all the methods.

Open VBA Editor

Before we start, here we age giving you a simple reminder to open the VBA Editor in Excel.

First, press Alt+F11 on your keyboard. Then, select Insert > Module. After that, it will open the VBA editor of Excel.

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

Now, this method basically finds the end of a range. Mainly, the last used cell range. We can use this method to find the last row with data in a given range. Using the VBA will give you the desired results.

📌 Steps

① First, open the VBA Editor.

② Then, type 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

③ Now, save the file. Then, press Alt+F8 to open the Macro dialog box. Select range_end_method

④ After that, click on Run.

As you can see, we have successfully found the last row with data in a range using VBA in Excel.

Read More: Excel VBA to Find Multiple Values in Range

2. Range.Find Property of VBA in Excel

Now, in VBA we use the Range.Find method to search for a particular value from a dataset. But this method comes in handy to find the last row with data in a range.  It works like the Find & Replace dialog box of Excel. Range. Find method has a lot of arguments. But we won’t use them all.

Before we use the Range.Find method, let’s give you a piece of quick information:

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

What:=”*” – The asterisk is a wildcard character that discovers any text or number in the cell.  It’s primarily the same as exploring for a non-blank cell.

SearchOrder:=xlByRows – This means Finding to dig through each whole row before moving on to the next. The direction is searched left-to-right or right-to-left depending on the SearchDirection argument. The additional option here is xlByColumns, which is utilized when locating the last column.

SearchDirection:=xlPrevious – This determines which direction to explore.  xlPrevious means it will search from right-to-left or bottom-to-top.  The other alternative is xlNext which shifts in the opposing path.

📌 Steps

① First, open the VBA Editor.

② Then, type 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

③ Now, save the file. Then, press Alt+F8 to open the Macro dialog box. Select range_find_method.

④ After that, click on Run.

Ultimately, it will find the last row with data in our Excel worksheet.

3. Using SpecialCells Function to Find Last Row Using VBA

This method works by pressing Ctrl+End on your keyboard. When you press Ctrl+End on your keyboard, it will always take you to the last row no matter where you are. But if you want to find the last used row with data using VBA codes in Excel, this code is a must for you.

📌 Steps

① First, open the VBA Editor.

② Then, type 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

③ Now, save the file. Then, press Alt+F8 to open the Macro dialog box. Select  specialcells_method.

④ After that, click on Run.

As you can see, we are successful in finding the last row with data using VBA in Excel.

4. Using UsedRange Function to Find Last Row with Data in a Range

The UsedRange in VBA is a possession of the worksheet that returns a range object representing the range used (all Excel cells used or loaded in a worksheet) on a particular worksheet. It is a property meaning the area covered or specified by top-left utilized cells and the last right used cells in a worksheet.

📌 Steps

① First, open the VBA Editor.

② Then, type 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

③ Now, save the file. Then, press Alt+F8 to open the Macro dialog box. Select usedRange_method.

④ After that, click on Run.

Finally, you will see the last used row on a worksheet in Excel successfully.

5. Using Table Range using VBA in Excel

If you have a table in your worksheet, you can find the last row with data with this method.

📌 Steps

① First, open the VBA Editor.

② Then, type 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: Here, we are adding 3 with the last row as our dataset started after row 3.

③ Now, save the file. Then, press Alt+F8 to open the Macro dialog box. Select TableRange_method.

④ After that, click on Run.

As you can see, we have successfully used the table range method in VBA codes to find the last row with data in Excel.

6. Use of the Named Range to Find Last Row with Data in a Range

This method is not commonly used in Excel. But, we think you should learn this to enrich your knowledge.

If your dataset has a named range, you can use this code. Take a look at the following screenshot. It has a named range in it.

📌 Steps

① First, open the VBA Editor.

② Then, type 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: We are adding 1 to the LastRow because our range started after row 1.

③ Now, save the file. Then, press Alt+F8 to open the Macro dialog box. Select nameRange_method.

④ After that, click on Run.

As you can see, we are successful to find the last row with data using VBA.

7. CurrentRegion Function of VBA in Excel

You can also use the CurrentRegion method of VBA to find the last used row in Excel. Though it is tricky, you can use this to your advantage if you want.

📌 Steps

① First, open the VBA Editor.

② Then, type 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: Range should be the first cell of your dataset. And add your rows number according to your choice. Here, we added 3 because our dataset started after row 3.

③ Now, save the file. Then, press Alt+F8 to open the Macro dialog box. Select CurrentRegion_method

④ After that, click on Run.

As you can see, we have successfully found the last row with data using the VBA code.

💬 Things to Remember

✎ Range.End only works on a single row or column. If your dataset contains a lot of blank cells, it will be difficult to find the last row with data.

✎ Sometimes, you have to add some values to your methods to run the code smoothly. We added row numbers to find the last cell. So, you have to remember from where your dataset started.

Download Practice Workbook

Download this practice workbook.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to find last row with data in a range using VBA in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!

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