Get The Last Used Row In VBA Using Range.Find

Here’s one of my favourite, custom functions, GetLastUsedRow.

Public Function GetLastUsedRow( _
                        ByVal rngToCheck As Range, _
                        Optional lngLookIn As XlFindLookIn = xlFormulas _
                                  ) As Long

    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'This function finds the last row containing data in a given range.'
    '                                                                  '
    'If the range is empty then the function                           '
    'returns the first row of the range.                               '
    '                                                                  '
    'The function will not find the last row correctly                 '
    'if it has been hidden by filtering                                '
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Const strTOFIND As String = "*"
    
    Dim rngLast As Range
    
    Set rngLast = rngToCheck.Find( _
                    What:=strTOFIND, _
                    LookIn:=lngLookIn, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious)

    If rngLast Is Nothing Then
        GetLastUsedRow = rngToCheck.Row
    Else
        GetLastUsedRow = rngLast.Row
    End If
    
End Function

It’s really flexible because it can be used on any type of range and, in particular, across multiple columns. Here are some examples:

Sub Examples()

    'from a block of cells
    MsgBox CStr(GetLastUsedRow(Sheet1.Range("D2:E8")))
    
    'from 3 entire columns
    MsgBox CStr(GetLastUsedRow(Sheet1.Range("D:F")))
    
    'from an entire sheet
    MsgBox CStr(GetLastUsedRow(Sheet1.UsedRange))
    
End Sub

The key points you need to be aware of are:

• If the range is empty then the function will return the first row of the range.
• If the last row has been hidden by filtering or only contains a cell prefix character then the function will not pick it up.
• In Excel 2002 or later the function can also be used as a UDF.

Advertisements