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.
Pingback: Get The Last Used Row In VBA Using Range.End | RAD Excel
Pingback: Self Extending UDFs – Part 2 | RAD Excel