5 Ways to Find Last Row Using Excel VBA

This tutorial will cover multiple ways to find last row in Excel using VBA.

A common task in VBA is to find the last row of a dataset. There are many ways to do this, which may create some confusion. From my perspective atleast, the answer lies in how our data is laid out and the convenience of the technique in consideration.

We will refer to the below dataset which is in sheet: Invoice (code name: wsInv). The worksheet will be referred to using its code name. i.e. Instead of using Thisworkbook.sheets(“Invoice”), we will use wsInv.

Solution 1: Range.End

Similar to using Ctrl + Up arrow key in Excel. We will look in column A. The advantage of this approach is that, its simple, short and intuitive. But, the drawbacks are that we can only look in single columns and the last row in a column may not be representative of the entire dataset.

Never the less, this is the technique I use most often.

wsInv.Range("A" & wsInv.Rows.Count).End(xlUp).Row

Solution 2: CurrentRegion

The Current Region of a data set, is the entire range of that data that is surrounded by blank rows and blanks columns. And we can determine the Current Region, by selecting any cell in that data and pressing Ctrl + A.

If your data starts from row 1, then we can just count the number of rows in the dataset and that will be equal to the last row used by that dataset in Excel.

But, if your data doesn’t start in the first row, then we need to do this the proper way. That is, by getting the row number of the last row using the index number of that row.

'If your data starts in row 1
lrow = wsInv.Range("A1").CurrentRegion.Rows.Count
'If your data doesn’t start in row 1
lrow = wsInv.Range("A2").CurrentRegion.Rows(wsInv.Range("A2").CurrentRegion.Rows.Count).Row
'We can shorten the code, by feeding the range into a range object.
Dim rng As Range
Set rng = wsInv.Range("A2").CurrentRegion
lrow = rng.Rows(rng.Rows.Count).Row

Solution 3: UsedRange

But, what if your data is separated by blank rows. Or, rather, you are not sure whether your data is separated by blank rows. We can use the Used Range property of the Worksheet object to grab the last row.

The advantage of using UsedRange is even if our data range has blank cells or blank rows or blank columns, we will still be able to locate the last used cell.

But, on the other hand, if the cell doesn’t have any values, but there is a formula or formatting in it, the UsedRange technique will still consider it as a Used Cell. Depending on what outcome you are after, this may or may not be what you want.

'If your data starts in row 1
lrow = wsInv.UsedRange.Rows.Count
'If your data doesn’t start in row 1
lrow = wsInv.UsedRange.Rows(wsInv.UsedRange.Rows.Count).row

Solution 4: SpecialCells

Excel has a menu full of options called Go To Special that allows us to select cells with special characteristics. In the Home ribbon, go to Find and Select. Then, select Go To special. We have a list of options here. Select Last Cell. Click Ok. The last used cell in our worksheet will be selected.

The advantage of using SpecialCells is even if our data range has blank cells or blank rows or blank columns, we will still be able to locate the last used cell.

And for that reason, this technique is superior to just using the Range.End property. Infact, it is similar to the Used Range property that we saw earlier except for a few subtle differences.

But, on the other hand, if the cell doesn’t have any values, but there is a formula or formatting in it, the SpecialCells technique will still consider it as a Used Cell. Depending on what outcome you are after, this may or may not be what you want.

lrow = wsInv.Range("A1").SpecialCells(xlCellTypeLastCell).row

But, there is one issue. If you update the data and try to run Special Cells without saving the changes in the workbook, Special Cells will still determine the used range based on the previous version of data. UsedRange doesn’t face this issue, and hence, I prefer to use it over SpecialCells.

Solution 5: Find

This is the most efficient technique of find the last row. But, the code is long winded and I seldom use it unless the situation calls for it.

We can go to our Home ribbon, choose Find and Select and choose Find. Or, just go Ctrl + F. We have five options here, which we can even specify through code. And there are even more options available in VBA. And some of them, will help us find our last row.

Dim lrow As Long
lrow = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Range("A1"), _
                LookIn:=xValues, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False _
                ).row

This technique is great in finding the last row with values, ignoring any formatting or formulas.

But, there is one issue. If there is no data, then Range.Find will throw a Run Time error. To counter that, we will need to add some error handling. If there is no data, we will skip the Find method and go to the next line of code. In this case, the last row variable will not get populated. And if it remains undefined as zero, we will return last row as row 1.

Dim lrow As Long
On Error Resume Next
lrow = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Range("A1"), _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False _
                ).row
If lrow = 0 Then
    lrow = 1
End If

Bonus Solution: Tables

Tables are awesome and I would highly recommend to use them whenever you can. And finding the last row is straightforward as well.

We’ll create a table for this example. Go to Insert ribbon and select Table or just press Ctrl T. Click ok. Go to the Table Design ribbon and give this table a name: InvoiceTable.

In VBA, table is referred to as a List Object. So, we can declare a List Object variable and assign our table to it.

If the first row of the table is the same as the first row of the Excel worksheet then we can just count the number of rows in the table, and that will be equal to the last row in the Excel worksheet.

But, what if the table doesn’t start from row 1. Then, we need to grab the row number of the last row using its index number.

Dim tblInv As ListObject
Set tblInv = wsTblInv.ListObjects("InvoiceTable")
Dim lrow As Long
'If your data starts in row 1
lrow = tblInv.Range.Rows.Count
'If your data doesn’t start in row 1
lrow = tblInv.Range.Rows(tblInv.Range.Rows.Count).row

Related Posts