使用 VBA 进行多列排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1450809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multi-column sort with VBA
提问by Echilon
I'm using VBA to sort columns in Excel 2003. I need to sort by column 5 ascending, then column 3 using a custom order, then by column 4 ascending. I'm having difficulty getting the sort to work and I don't totally understand how OrderCustom applies.
我使用 VBA 对 Excel 2003 中的列进行排序。我需要按第 5 列升序排序，然后使用自定义顺序按第 3 列排序，然后按第 4 列升序排序。我很难让排序工作，我不完全理解 OrderCustom 是如何应用的。
Any pointers in the right direction would be appreciated :) My code is below.
任何指向正确方向的指针将不胜感激:) 我的代码如下。
With wsData
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
lastCol = .Cells(4, Columns.Count).End(xlToLeft).Column
Dim n As Long
Application.AddCustomList ListArray:=Array("LOW", "MEDIUM OR HIGH", "HIGH ONLY")
n = Application.GetCustomListNum(Array("LOW", "MEDIUM OR HIGH", "HIGH ONLY")) + 1
Dim strSortOrder As String
.Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
Key1:=.Range(.Cells(2, 5), .Cells(lastrow, lastCol)), Order1:=xlAscending, _
Key2:=.Range(.Cells(2, 3), .Cells(lastrow, lastCol)), Order2:=xlDescending, _
Key3:=.Range(.Cells(2, 4), .Cells(lastrow, lastCol)), Order3:=xlDescending, _
OrderCustom:=n, _
MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes
End With
回答by Adam Ralph
Try splitting your sort into 3 seperate steps, with only the second one using your custom sort order, i.e.
尝试将您的排序分成 3 个单独的步骤，只有第二个使用您的自定义排序顺序，即
.Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
Key1:=.Range(.Cells(2, 4), .Cells(lastrow, lastCol)), Order1:=xlDescending, _
MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes
.Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
Key1:=.Range(.Cells(2, 3), .Cells(lastrow, lastCol)), Order1:=xlDescending, _
OrderCustom:=n, _
MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes
.Range(.Cells(1, 1), .Cells(lastrow, lastCol)).Sort _
Key1:=.Range(.Cells(2, 5), .Cells(lastrow, lastCol)), Order1:=xlAscending, _
MatchCase:=False, Orientation:=xlSortColumns, Header:=xlYes
Note that I have reversed the order in which these sorts are performed in comparison with how they are declared in the original statement.
请注意，与它们在原始语句中的声明方式相比，我颠倒了执行这些排序的顺序。
