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

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel, Microsoft Office and tagged , , , . Bookmark the permalink.

2 Responses to Get The Last Used Row In VBA Using Range.Find

  1. Pingback: Get The Last Used Row In VBA Using Range.End | RAD Excel

  2. Pingback: Self Extending UDFs – Part 2 | RAD Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s