vba - Excel宏动态范围

Question

录制宏时，我的数据是 4162，这就是为什么每次我的数据超过 4162 时，我都会设置范围(“A1:A4162”)，它将无法工作。我需要代码来选择我在工作表中提供的任何数据范围

  Range("A1").Select
        Selection.AutoFilter
        ActiveWorkbook.Worksheets("Incident Ticket").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Incident Ticket").AutoFilter.Sort.SortFields.Add _
            Key:=Range("A1:A4162"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Incident Ticket").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

Answer 1

我添加了要包含在您的代码中的函数lastRow。这将自动为您找到最后一行。另外，稍微清理一下你的代码。

Sub Test()

    Dim ws As Worksheet, rng As Range
    Set ws = ThisWorkbook.Worksheets("Incident Ticket")
    Set rng = ws.Range("A1:A" & lastRow(ws))

    rng.AutoFilter
    With ws.AutoFilter
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=rng, SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
        .Sort.Header = xlYes
        .Sort.MatchCase = False
        .Sort.Orientation = xlTopToBottom
        .Sort.SortMethod = xlPinYin
        .Sort.Apply
    End With

End Sub

Function lastRow(ws As Worksheet, Optional col As Variant = 1) As Long
    With ws
        lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
End Function