XLA routines: EE_GetLastPopulatedCell

Nick's picture
Finds the last populated cell on a worksheet, or an empty cell representing the max row and max col.
Function EE_GetLastPopulatedCell(Optional wks As Worksheet) As Range
'-          Works how specialcells (lastCell)  SHOULD work
'-          Returns single cell range
    Dim lngCol      As Long
    Dim lngMaxRow   As Long
    Dim lngRow      As Long
    Dim lngMaxCol   As Long
'http://excelexperts.com/xla-routines-eeGetLastPopulatedCell for updates on this function

    If wks Is Nothing Then
        Set wks = ActiveSheet
    End If
    If wks.UsedRange.Rows.Count = 1 And wks.UsedRange.Columns.Count = 1 Then
        Set EE_GetLastPopulatedCell = wks.UsedRange.Cells(1, 1)
        Exit Function
    End If
    lngMaxRow = wks.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lngMaxCol = wks.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set EE_GetLastPopulatedCell = wks.Cells(lngMaxRow, lngMaxCol)
End Function