VBA Excel find functions: last row in worksheet or range

By far the easiest and most reliable function to find the highest row number, i.e. the “last row” containing a cell with content in a given worksheet or range. Returns zero if:

The worksheet or range is empty (contains no cells with any content).
Neither a worksheet nor a range object was passed to the function.

Table of Contents

Function
Parameters
Usage example

Function

VBA
Function LastRow(Optional ws As Worksheet, Optional rng As Range) As Long
    On Error Resume Next
    If Not rng Is Nothing Then
        LastRow = rng.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    ElseIf Not ws Is Nothing Then
        LastRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    End If
End Function

Parameters

Name	Optional/ Required	Data type	Description
ws	Optional	worksheet	The worksheet in which the last content row is to be found.
rng	Optional	range	The cell range in which the last content row is to be found.

Usage example

VBA
Sub TestLastRow()
    Dim LR As Long
    LR = LastRow(ActiveSheet)
    Debug.Print "Last row in active sheet: " & LR
    LR = LastRow(, ActiveSheet.Range("A1:E100"))
    Debug.Print "Last row in range A1:E100 of active sheet: " & LR
End Sub