vba - 按行排序

Question

有没有办法通过宏来模仿 Excel 中的排序命令(以下示例)？

我有 60 行和 53 列的数据。使用排序命令后，我确实能够得到我想要的结果，但相信如果我可以有一个宏，它会节省我很多时间。您可以查看之前和之后的图像，以直观地了解我一直在努力实现的目标。

[ ]

[ ]

例如

SORT LEFT TO RIGHT

SORT BY [ROW 2] | SORT ON [VALUES] | ORDER [SMALLEST TO LARGEST]
SORT BY [ROW 3] | SORT ON [VALUES] | ORDER [SMALLEST TO LARGEST]
...
...
SORT BY [ROW 59] | SORT ON [VALUES] | ORDER [SMALLEST TO LARGEST]
SORT BY [ROW 60] | SORT ON [VALUES] | ORDER [SMALLEST TO LARGEST]

更新:我运行了宏记录器来看看它会是什么样子，这是从第 2 行到第 61 行的缩短代码。

Sub Macro3()
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B2:BB2") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B3:BB3") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B4:BB4") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B5:BB5") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ...MORE ROWS HERE IN THE PATTERN AS SHOWN
    ...MORE ROWS HERE IN THE PATTERN AS SHOWN
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B57:BB57" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B58:BB58" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B59:BB59" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B60:BB60" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet11").Sort.SortFields.Add Key:=Range("B61:BB61" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet11").Sort
        .SetRange Range("B1:BB63")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

有什么办法可以让这个更简洁吗？

Answer 1

您可以直接在 Excel 中按行排序，无需 VBA:

Data->Sort->Options...->Sort left to right

我想从 vba 排序，指定行作为条件字段:

Sub SortByRows()
    With ActiveSheet.UsedRange
        .Sort Key1:=.Rows(1), Key2:=.Rows(2), Key3:=.Rows(3)
    End With
End Sub

不过，您的标准数量有限。由于您需要更多条件字段，因此比较棘手:

Sub SortByRows()
    With ActiveSheet
        Set target = .Range(.Cells(2, 2), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count))
    End With
    With ActiveSheet.Sort
        .SortFields.Clear
        .SetRange target
        .Orientation = xlLeftToRight
        .Header = xlNo

        For Each r In target.Rows
           .SortFields.Add r
        Next
        .Apply
    End With
End Sub