How To Find Last Row using Find Method [Excel VBA]

Home

This tutorial will cover how to find the Last Used Row Using Find Method in Excel VBA.

The Find method is the most complete and efficient method to find the last used row. However, there are a lot of arguments to fill out, which makes the code rather long. If you can get past that, then this is the method to use.

There are many ways to find the last row using VBA. While this article just focuses on Find, be sure to check out our blog posts on the other methods as well.

Find last row using:

1. Range.End
2. CurrentRegion and UsedRange
3. SpecialCells
4. Table

Basic: Last Row using Find in VBA

Note: In all examples, the worksheet code name wsInv is being used. You could refer to the worksheet directly instead: ThisWorkbook.Sheets(“Invoice”)

We will fill out 7 arguments in our Find method.

'Find the last row in Excel VBA using Find method
Sub Find_Last_Row()
Dim lrow As Long
lrow = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Range("A1"), _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False _
                ).row
End Sub

1. What: We want to search for any text or number, so we can use the asterix symbol.
2. After: We want to start our search from the very first cell which is A1.
3. LookIn: Our two main options for what to look in is Values and formulas. Choose this one carefully. If you want to find the last cell with data in it, choose xlValues. If you want to find the last cell which can be a formula or value, then choose xlFormulas.
4. LookAt: You can match just a part of the cell or the whole cell. We will choose xlPart.
5. SearchOrder: Depends on whether you are searching for the row number or column number. We want the row. So, we will choose row.
6. SearchDirection: The most important option here is the Search Direction. You would be tempted to just choose Next. But, that will give you the next used row. To search for the last used row, we need to go in the opposite direction i.e. we will scan the worksheet (by row) from the very bottom, till we come across a used cell. So, choose xlPrevious.
7. MatchCase: Can be True or False. Since we are searching for a wildcard, it doesnt really matter. We will just choose False.

We will drop LookAt and MatchCase in all our next code, since it wont affect our search result for the last used row.

In VBA, to find the last column, the code is the same as the last row. We will just change the Search Order and return a column instead of a row.

Sub Find_Last_Column()
Dim lcol As Long
lcol = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Cells(1), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious _
                ).Column
End Sub

If there is no data, the find method will throw a run time error. We will need to add some error handling to ignore that error and return 1 as the last row, if an error occurs i.e. if there is no data.

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

Example 1: Identify and Select Data Range

Here, we will assume that we know that there is data in this worksheet, but we just don’t know where it is. i.e. where it starts from and where it ends. This is the most thorough test of any method to find the last row, and it is also, where the Find method shines above the rest of the methods.

Besides finding the last row and last column, the Find method can also be used to find the first row and first column. We just need to change the After option to start from the last cell in the worksheet and the Search Direction to Next i.e. we will perform the exact opposite search compared its the last row/ column counterpart.

Sub Find_Last_Row()
'Find last row and last column
Dim lastrow As Long
lastrow = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Cells(1), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious _
                ).row
Dim lastcol As Long
lastcol = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Cells(1), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious _
                ).Column
'Find first row and first column
Dim firstrow As Long
firstrow = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Cells(wsInv.Rows.Count, wsInv.Columns.Count), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext _
                ).row
Dim firstcol As Long
firstcol = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Cells(wsInv.Rows.Count, wsInv.Columns.Count), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext _
                ).Column
' Select the data range
wsInv.Range(wsInv.Cells(firstrow, firstcol), wsInv.Cells(lastrow, lastcol)).Select
End Sub

Example 2: Copy Data Range to New Sheet

We will copy data from current sheet wsInv onto sheet: CopyTo (code name: wsCopyTo).

Sub Find_Last_Row()
wsCopyTo.Cells.Clear
Dim lastrow As Long
lastrow = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Cells(1), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious _
                ).row
wsInv.Range("A1:J" & lastrow).Copy wsCopyTo.Range("A1")
wsCopyTo.Columns.AutoFit
End Sub

Example 3: Paste Data at Bottom of Current Data Range

We will copy data from sheet: CopyFrom (code name: wsCopyFrom) and paste it at bottom of our data range in wsInv sheet.

Sub Find_Last_Row()
'Find last used row in current dataset
Dim offrowInv As Long
offrowInv = wsInv.Cells.Find(What:="*", _
                After:=wsInv.Cells(1), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious _
                ).row
'Offset it by one, to get the first unused row
offrowInv = offrowInv + 1
'Find last row of data that we want to copy
Dim lrowCopyFrom As Long
lrowCopyFrom = wsCopyFrom.Cells.Find(What:="*", _
                After:=wsCopyFrom.Cells(1), _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious _
                ).row
'Copy and paste the data
wsCopyFrom.Range("A2:J" & lrowCopyFrom).Copy wsInv.Range("A" & offrowInv)
End Sub