Saturday, February 1, 2014

Excel-VBA: Find Last Cell of Active WorkSheet

Hello here is the code

Public Function GetLastCellAddress() As String
    ' Variables
    Dim intCol As Integer
    Dim lngRow As Long
    Dim rUsedRange As Range
    Dim rCell As Range

    ' first unlock all cells
    Cells.Locked = False
    If WorksheetFunction.CountA(Cells) > 0 Then
        lngRow = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        intCol = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set rUsedRange = ActiveSheet.Range("$A$1" & ":" & Cells(lngRow, intCol).Address)
       
        lngRow = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        intCol = Cells.Find(what:="*", After:=[A1], _
                            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set rUsedRange = ActiveSheet.Range("$A$1" & ":" & Cells(lngRow, intCol).Address)
        GetLastCellAddress = rUsedRange.Address
    End If
End Function

No comments: