Determining The Last Non-empty Cell In A Column Or Row

Category: VBA Functions | [Item URL]

This tip presents two useful VBA functions that can be used in worksheet formulas. LASTINCOLUMN returns the contents of the last non-empty cell in a column; LASTINROW returns the contents of the last non-empty cell in a row. Each function accepts a range as its single argument. The range argument can be a complete column (for LASTINCOLUMN) or a complete row (for LASTINROW). If the supplied argument is not a complete column or row, the function uses the column or row of the upper left cell in the range. For example, the following formula returns the last value in column B:

=LASTINCOLUMN(B5)

The formula below returns the last value in row 7:

=LASTINROW(C7:D9)

You'll find that these functions are quite fast, since they only examine the cells in the intersection of the specified column (or row) and the worksheet's used range.

The LASTINCOLUMN function

Function LASTINCOLUMN(rngInput As Range)
    Dim WorkRange As Range
    Dim i As Long, CellCount As Long
    Application.Volatile
    Set WorkRange = rngInput.Columns(1).EntireColumn
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINCOLUMN = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function

The LASTINROW function

Function LASTINROW(rngInput As Range) As Variant
    Dim WorkRange As Range
    Dim i As Long, CellCount As Long
    Application.Volatile
    Set WorkRange = rngInput.Rows(1).EntireRow
    Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
    CellCount = WorkRange.Count
    For i = CellCount To 1 Step -1
        If Not IsEmpty(WorkRange(i)) Then
            LASTINROW = WorkRange(i).Value
            Exit Function
        End If
    Next i
End Function