vba - 如何将一系列行排序到一定数量的(未知)列？

Question

如果我将范围的初始部分设置为 Range("A:A")，如何确保将整行传递给排序？

数据
id、fname、mname、lname、后缀、状态、位置、时区

通过在 id 中搜索起点和终点来选择范围。
因此 Range 可能是一张纸上的 A1:183 和另一张纸上的 A1:A1138。列以 G 或 H 结尾。数据仍然需要“按摩”

在每张纸上，我按 D(lname) 和 B(fname) 进行排序，并且我想包括剩余的列，因此可以保留数据完整性。

到目前为止，我有一个选择范围的子程序，但我不知道如何在没有 rng.EntireRow.Select 的情况下标记其他列，这似乎不起作用。

Sub sortRows(bodyName As String, ByRef wksht As Worksheet)
Dim operationalRange As Range, sortRange As Range

Set operationalRange = selectBodyRow(bodyName).EntireRow

Debug.Print "Sorting Worksheet " & wksht.Name & " containing " & operationalRange.Count & " rows."
ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Add Key:=operationalRange, _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(wksht.Name).Sort.SortFields.Add Key:=operationalRange, _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(wksht.Name).Sort
      .SetRange operationalRange
      .Header = xlGuess
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply   ' Fails here with:
               ' Runtime Error '1004':
               ' The sort reference is not valid. Make sure that it's within the data
               ' you want to sort, and the first Sort By box isn't the same or blank.
    End With
End Sub

Answer 1

您需要找到最后一行和最后一列来构建您的范围。

例如

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LRow As Long, LCol As Long
    Dim rng As Range

    '~~> Change this with thee relevant sheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")

    '~~> Get Last Row and Last Column
    LRow = LastRow(ws)
    LCol = LastColumn(ws)

    With ws
        '~~> Define your range
        Set rng = .Range("A1:" & ReturnName(LCol) & LRow)

        Debug.Print rng.Address
    End With
End Sub

'~~> Function to get last row
Public Function LastRow(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastRow = wks.Cells.Find(What:="*", _
    After:=wks.Range("A1"), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
End Function

'~~> Function to get last column
Public Function LastColumn(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastColumn = wks.Cells.Find(What:="*", _
    After:=wks.Range("A1"), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Column
End Function

'~~> Function to get the Column name from Column Number
Function ReturnName(ByVal num As Integer) As String
    ReturnName = Split(Cells(, num).Address, "$")(1)
End Function