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

While working with Microsoft Excel, finding 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.


Download Practice Workbook

Download this practice workbook.


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

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.

📕Read More: Find the Last Cell With Value in Row in Excel (6 Methods)

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

dataset to find last row with data in range using vba in excel

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.

insert vba module

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.

final output of excel vba find last row with data in range

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

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.

VBA Macro dialog dialog box

After that, click on Run.

final out put of excel vba find last row with data in range

In the end, 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 like 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.

outcome of excel vba find last row with data in range

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.

VBa macro dialog box

After that, click on Run.

out put of vba find last row with data in excel

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.

final output of excel vba find last row with data in range

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.

named range of dataset

📌 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.

final output

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.

result of excel vba find last row with data in range

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.


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

Tags:

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo