Find Last Cell

Finding the last cell is a common thing people look for when they are coding in VBA. I made a function that does this automatically for me when I am looking for the last cell. In conjunction with the RRange function this simplifies the programming life for VBA significantly.

Heres some example code on how to use the function FindEnd.

Sub FindEndExamples() 
    Dim l As Long 

    'Find last row in column 2 on current worksheet 
    l = FindEnd(2)
    Debug.Print l 

    'Find last column in row 5 on current worksheet 
    l = FindEnd(5, 2)
    Debug.Print l

    'Find very last row in current worksheet
    l = FindEnd()
    Debug.Print l

    'Find very last column in current worksheet 
    l = FindEnd(, 2) 
    Debug.Print l

End Sub

And here’s the actual FindEnd Function.


' --------------------------------------------------------------
'Comments:  This function finds the last row or column in a
'           specified row or colum, if no row or column specified will find last
'           row or column in worksheet.
'
'Arguments: SearchColumnNumber    The row or column to be searched.
'           Row1Col2        1 will find the last row.
'                           2 will find the last column.
'           oRange             Worksheet to be operated on.
'           FindEnd         Returns long
'
'Original Source: http://www.mrexcel.com/forum/showthread.php?t=74317 4/22/2009
'Date           Developer   History
'--------------------------------------------------------------
'04/22/09       Jon Nyman   Initial Version
'03/09/10       Jon Nyman   Changed to returning long instead of integer.  Changed input variable names.
'06/23/10       Jon Nyman   Changed to using "Pivot Table Data Crunching Method" (pg. 202) method of find
'               the last cell in application end.  Added a check to see if last cell is filled.
'07/14/10       Jon Nyman   Added optional workbook.
'11/08/10       Jon Nyman   Added ability to get the last row/column without specifying row or column (using find).
'                           Changed byval to byref to increase speed.
'09/26/11       Jon Nyman   Changed so only need to add worksheet object.
'03/27/12       Jon Nyman   Added ability to find last column or row in a range.
Public Function FindEnd(Optional ByVal SearchColumnNumber As Long = 0, Optional ByVal Row1Col2 As Integer = 1, _
    Optional ByVal oRange As Object = Nothing) As Long
    
    Dim i As Long
    Dim sAddress As String
    
    On Error GoTo ExitFindEnd
    
    If oRange Is Nothing Then
        Set oRange = ActiveSheet
    End If
        
    If Row1Col2 = 2 Then
        If SearchColumnNumber = 0 Then
            FindEnd = oRange.Cells.Find("*", oRange.Range("A1"), xlFormulas, XlLookAt.xlWhole, xlByColumns, xlPrevious, False, False).Column
        Else
            If Mid$(oRange.Parent.Name, Len(oRange.Parent.Name) - 3, 1) = "." Then
                i = 256
            Else
                i = Application.Columns.Count
            End If
            If LenB(oRange.Cells.Item(SearchColumnNumber, i)) > 0 Then
                FindEnd = i
            Else
                FindEnd = CLng(oRange.Range(RangeAddress(SearchColumnNumber, i)).End(xlToLeft).Column)
            End If
        End If
    Else
        If SearchColumnNumber = 0 Then
            FindEnd = oRange.Cells.Find("*", oRange.Range("A1"), xlFormulas, XlLookAt.xlWhole, xlByRows, xlPrevious, False, False).Row
        Else
            If Mid$(oRange.Parent.Name, Len(oRange.Parent.Name) - 3, 1) = "." Then
                i = 65536
            Else
                i = Application.Rows.Count
            End If
            If LenB(oRange.Cells.Item(i, SearchColumnNumber)) > 0 Then
                FindEnd = i
            Else
                FindEnd = CLng(oRange.Range(RangeAddress(i, SearchColumnNumber)).End(xlUp).Row)
            End If
        End If
    End If
    
ExitFindEnd:
If Err.Number <> 0 Then
    FindEnd = -1
End If

End Function