查找最后一行和最后一列

Sub Sht1Rng()
    Dim ws As Worksheet
    Dim numofrows As Long
    Dim numofcols As Long
    Dim myrange As Range
    Set ws = Sheets("Sheet1")
    With ws
        numofrows = .Cells(.Rows.Count, "AE").End(xlUp).Row
        numofcols = .Cells(4, .Columns.Count).End(xlToLeft).Column
        Set myrange = .Range(.Cells(4, 1), .Cells(numofrows, numofcols))
    End With
    MsgBox myrange.Address

End Sub

您也可以使用此代码。

Sub SelectLastCellInInSheet()
    Dim Rws As Long, Col As Integer, r As Range, fRng As Range
    Set r = Range("A1")
    Rws = Cells.Find(what:="*", after:=r, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Col = Cells.Find(what:="*", after:=r, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Set fRng = Range(Cells(2, 1), Cells(Rws, Col))    ' range A2 to last cell on sheet
    fRng.Select    'or whatever you want to do with the range
End Sub

除了我上面的评论，这是你正在尝试的吗？

Sub ReportArea()
    Dim ws As Worksheet
    Dim Lrow As Long
    Dim myrange As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last row of COl AE. Change it to the relevant column
        Lrow = .Range("AE" & .Rows.Count).End(xlUp).Row

        Set myrange = .Range("A4:AE" & Lrow)

        With myrange
            '
            '~~> Do whatever you want to do with the range
            '
        End With
    End With
End Sub

注意：也不需要选择区域/工作表，直接使用对象即可。Interesting Read

已发布的替代解决方案：
1：

Dim LRow&, LColumn&
Lrow = Sheets("SheetName").Cells.SpecialCells(xlCellTypeLastCell).Row
LColumn = Sheets("SheetName").Cells.SpecialCells(xlCellTypeLastCell).Column
MsgBox "Last Row is: " & Lrow & ", Last Column is: " & LColumn

二：

Dim x As Range
Set x = Range(Split(Sheets("SheetName").UsedRange.Address(0, 0), ":")(1))
MsgBox "Last Row is: " & x.Row & ", Last Column is: " & x.Column

输出结果