vba 使用动态范围和 customOrder excel 排序宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12095053/
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 Macro with dynamic range and customOrder excel
提问by Jim
I am trying to make a multi-level sort Macro, while the amount of rows is dynamic. I've been messing around with some test data to try and get it to work, however I keep getting 1004 run-time errors from Excel. Hoping a second pair of eyes will help figure this out.
我正在尝试制作一个多级排序宏，而行数是动态的。我一直在处理一些测试数据以尝试使其正常工作，但是我不断收到来自 Excel 的 1004 运行时错误。希望第二双眼睛能帮助解决这个问题。
Here is the test data
这是测试数据
A B C D
Num Status Junk Junk
1 Open 1 1
2 Open 2 2
3 Closed 3 3
3 Open 3 3
4 Open 4 4
6 Open 6 6
8 Open 8 8
8 Open 8 8
34 Open 34 34
456 Open 456 456
5 Closed 5 5
853 Open 853 853
2345Closed 2345 2345
234 Open 234 234
23 Closed 23 23
4 Open 4 4
76 Closed 76 76
345 Open 345 345
623 Closed 623 623
523 Open 523 523
4525Closed 4525 4525
6 Open 6 6
And here is my Macro
这是我的宏
Range("A1:D1").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
Key:=Range("B1:B10"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Open,Closed", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
Key:=Range("A1:A10"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Also see the Line where i have Range("A1:A10"), want A10 to be the last row. So I was unsure what to put there.
另请参阅我有 Range("A1:A10") 的行，希望 A10 是最后一行。所以我不确定在那里放什么。
Thanks all,
谢谢大家，
Jim
吉姆
回答by Scott Holtzman
Place your last row into a variable, then use that variable in your range name, like I've done below. I've also cleaned up your code a bit to make it easier to read and more efficient. You were getting Run-Time error, because you were missing the SetRangemethod:
将您的最后一行放入一个变量中，然后在您的范围名称中使用该变量，就像我在下面所做的那样。我还对您的代码进行了一些清理，使其更易于阅读和更高效。您收到运行时错误，因为您缺少该SetRange方法：
Sub mySort()
Dim lngLast As Long
lngLast = Range("A" & Rows.Count).End(xlUp).Row
On Error Go to Whoa! 'in honor of Siddhart Rout (I like that :))
With Worksheets("Sheet1").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B1:B" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Open,Closed", DataOption:=xlSortNormal
.SortFields.Add Key:=Range("A1:A1" & lngLast), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A1:D" & lngLast)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Go To 0
Exit Sub
Whoa!:
Msgbox "Error " & Err.Number & " " & Err.Description, vbOkOnly
End Sub
