排序多列excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/52619676/
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
sort multiple columns excel VBA
提问by Natalia Fontiveros
this is my code
这是我的代码
Sub SortMultipleColumns(myline As String)
With Worksheets("Result-Inactive").Sort
.SortFields.Add Key:=Range("A1"), Order:=xlAscending
.SortFields.Add Key:=Range("D1"), Order:=xlAscending
.SortFields.Add Key:=Range("J1"), Order:=xlAscending
.SetRange Range("A1:C" & myline)
.Header = xlYes
.Apply
End With
End Sub
I get the following error and I dont undertsand why "Run time error '1004' the sort reference is not valid. MAke sure that it is within the daya you want to sort, and then first Sort by Box isnt the same or blank. when I click debug. The .apply gets higlighted
我收到以下错误，我不明白为什么“运行时错误‘1004’排序引用无效。请确保它在您要排序的日期内，然后首先按框排序不相同或空白。当我点击调试。.apply 被高亮显示
any suggestions?
有什么建议？
回答by jamheadart
Just reiterating Jeeped's answer here but with a slightly different take:
在这里重申 Jeeped 的回答，但略有不同：
1) mylineshould really be defined as a Long or Integer
1)myline应该真正定义为 Long 或 Integer
2) The ranges declared with Key:=Range("A1")should be defined as the same worksheet
2) 声明的范围Key:=Range("A1")应定义为同一个工作表
3) The keys for Dand Jare outside of the .setRangewhich again, should be defined as being on the same worksheet also
3） 和 的键D又J在.setRange其中，也应该定义为在同一个工作表上
I've stuck with your same code but added the wsworksheet definition to all ranges, and changed your set range to include up to column J
我坚持使用相同的代码，但将ws工作表定义添加到所有范围，并将您的设置范围更改为最多包含列J
Sub SortMultipleColumns(myline As Long)
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Result-Inactive")
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=ws.Range("A1"), Order:=xlAscending
.SortFields.Add Key:=ws.Range("D1"), Order:=xlAscending
.SortFields.Add Key:=ws.Range("J1"), Order:=xlAscending
.SetRange ws.Range("A1:J" & myline)
.Header = xlYes
.Apply
End With
End Sub
I'm assuming mylineis there because sometimes you only want to sort the top set of a range of data. I also added a line to clearall the sortfields, just in case you run many different sorters on this sheet.
我假设myline存在是因为有时您只想对一系列数据的顶部集进行排序。我还在clear所有排序字段中添加了一行，以防万一您在此工作表上运行许多不同的排序器。
回答by Natalia Fontiveros
You are trying to include columns D and J as secondary sorting criteria but excluding them from the sorted range. Additionally, The Range("xn")do not necessarily belong to the Result-Inactive worksheet without being syntaxed as .Range("xn").Try the alternate VBA sort instead of the method produced by the 'macro' recorder.
您正在尝试将 D 列和 J 列包含为次要排序条件，但将它们从排序范围中排除。此外， TheRange("xn")不一定属于 Result-Inactive 工作表，除非被语法化为 。尝试.Range("xn")替代 VBA 排序，而不是“宏”记录器生成的方法。
Sub SortMultipleColumns()
With Worksheets("Result-Inactive")
with .cells(1, "A").currentregion
.Cells.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Key2:=.Range("D1"), Order2:=xlAscending, _
Key3:=.Range("J1"), Order3:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlYes
end with
End With
End Sub
