Two special functions written to find the last row and last column of a given Excel worksheet. These get around the limitations of using properties like ActiveSheet.UsedRange.Rows.Count in that it will ignore columns that have been touched or formatted, but that have no values. Full Disclosure: The LastRow__lng function is not my own work but wo…

vbaLastFunctions

Public Function LastRow__lng(ByRef wksWorksheet As Worksheet) As Long
''Stores the last row with data on it
Dim lngLowestRow As Long
''Temp to hold the last row of each column
Dim lngTempRow1 As Long
Dim lngTempRow2 As Long
''Greater value of Dim lngTempRow1 & lngTempRow2
Dim lngColRange As Long
''Used as counter in loop
Dim objColumn As Object
''Checks each column for last cell used in UsedRange
For Each objColumn In wksWorksheet.UsedRange.Columns
On Error Resume Next
''Finds the last column used for Constants
With objColumn.SpecialCells(xlCellTypeConstants)
lngTempRow1 = Range(.Areas(1), .Areas(.Areas.Count)).Rows.Count + _
Range(.Areas(1), .Areas(.Areas.Count)).Row - 1
End With
''Finds the last column used for Formulas
With objColumn.SpecialCells(xlCellTypeFormulas)
lngTempRow2 = Range(.Areas(1), .Areas(.Areas.Count)).Rows.Count + _
Range(.Areas(1), .Areas(.Areas.Count)).Row - 1
End With
On Error GoTo 0
If lngTempRow2 > lngTempRow1 Then lngTempRow1 = lngTempRow2
If lngTempRow1 > lngLowestRow Then lngLowestRow = lngTempRow1
Next objColumn
''Returns last row used in range
LastRow__lng = lngLowestRow
End Function

Public Function LastCol__lng(ByRef wksWorksheet As Worksheet) As Long

''Stores the last column with data on it
Dim lngFarthestCol As Long

''Temp to hold the last column of each row
Dim lngTempCol1 As Long
Dim lngTempCol2 As Long

''Greater value of Dim lngTempCol1 & lngTempCol2
Dim lngRowRange As Long

''Used as counter in loop
Dim objRow As Object

''Checks each row for last cell used in UsedRange
For Each objRow In wksWorksheet.UsedRange.Rows
    On Error Resume Next

    ''Finds the last column used for Constants
    With objRow.SpecialCells(xlCellTypeConstants)
        lngTempCol1 = Range(.Areas(1), .Areas(.Areas.Count)).Columns.Count + _
        Range(.Areas(1), .Areas(.Areas.Count)).Column - 1
    End With
    
    ''Finds the last column used for Formulas
    With objColumn.SpecialCells(xlCellTypeFormulas)
        lngTempCol2 = Range(.Areas(1), .Areas(.Areas.Count)).Columns.Count + _
        Range(.Areas(1), .Areas(.Areas.Count)).Column - 1
    End With

    On Error GoTo 0
    If lngTempCol2 > lngTempCol1 Then lngTempCol1 = lngTempCol2
    If lngTempCol1 > lngFarthestCol Then lngFarthestCol = lngTempCol1
Next objRow

''Returns last row used in range
LastCol__lng = lngFarthestCol
End Function

Just a note for anyone who stumbles upon this: These routines can be extremely unperformant in newer versions of Excel (due to greatly increased row and column counts). Several users reported crashes (which were repeatable), so I reverted to another method of finding the last row of the sheet.

That said, they have always been performant on any machines I'm running (weirdly).