Excel VBA 自定义排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20348579/
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
Excel VBA custom sorting
提问by maykel
I have some sheet named for example ("CZLON") as below. It contains some data imported from the txt files or created manually:
我有一些名为（“CZLON”）的工作表，如下所示。它包含一些从 txt 文件导入或手动创建的数据：
I need that data to be sorted in a specific way:
我需要以特定方式对数据进行排序：
First priority, by column "E" (if cell contains text with "S355" - 1st or "S235" - 2nd)
第一优先级，按列“E”（如果单元格包含带有“S355” - 1st 或“S235” - 2nd 的文本）
Second, by column "D". There is much more than 255 items in list so the standard excel sorting is not working properly.
其次，按“D”列。列表中有超过 255 个项目，因此标准 excel 排序无法正常工作。
I have created something like this (forgive me if something is wrong, I`m total VBA amateur):
我已经创建了这样的东西（如果有什么问题，请原谅我，我完全是 VBA 业余爱好者）：
Sub Sortuj_Czlon()
Application.ScreenUpdating = False
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("E4" & Cells(Rows.Count, "E").End(xlUp).Row _
), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="S355,S235", _
DataOption:=xlSortNormal
'//////////////////////////////////////////////////////////////////////////////////////////////////////////////
ActiveSheet.Sort.SortFields.Add Key:=Range("D4" & Cells(Rows.Count, "E").End(xlUp).Row _
), SortOn:=xlSortOnValues, Order:=xlDescending, CustomOrder:= _
"20x3,25x3,25x4,30x3,30x4,35x3,35x4,35x5,40x3,40x4,40x5,45x30x4,45x30x5,45x4," _
& "45x4,5,45x5,45x6,50x30x3,50x4,50x5,50x6,50x7,55x5,55x6,60x40x5,60x40x6,60x40x7,60x4," _
& "60x5,60x6,60x7,60x8,65x5,65x6,65x7,65x8,65x9,70x45x5,70x50x6,70x50x7,70x50x8,70x6," _
& "70x7,70x8,70x9,75x50x6,75x50x7,75x50x8,75x50x9,75x4,75x5,75x6,75x7,75x8,75x9," _
& "75x10,75x11,75x12,80x40x6,80x40x8,80x60x8,80x65x6,80x65x8,80x65x10,80x5,80x6," _
& "80x7,80x8,80x10,90x60x6,90x60x8,90x6,90x7,90x8,90x9,90x10,90x11,100x50x6,100x50x8," _
& "100x50x10,100x65x7,100x65x8,100x65x9,100x65x10,100x75x7,100x75x8,100x75x9,100x6,100x7," _
& "100x8,100x10,100x12,110x70x10,110x70x12,110x8,110x10,120x80x8,120x80x10,120x80x12,120x8," _
& "120x10,120x11,120x12,120x13,120x14,120x15,125x75x8,125x75x9,125x75x10,125x75x12,130x65x8," _
& "130x65x10,130x65x12,130x90x10,130x90x12,130x90x14,130x10,130x12,130x13,130x14,130x15,130x16," _
& "140x90x8,140x90x10,140x10,140x12,140x13,140x15,150x75x9,150x75x10,150x75x11,150x90x10,150x90x12," _
& "150x100x6,150x100x10,150x100x11,150x100x12,150x100x14,150x10,150x12,150x13,150x14,150x15,150x16,150x18," _
& "160x80x10,160x80x12,160x80x14,160x12,160x15,160x17,160x18,180x90x10,180x90x12,180x90x14,180x14," _
& "180x15,180x16,180x18,200x100x10,200x100x12,200x100x14,200x100x16,200x16,200x18,200x20,250x90x10," _
& "250x90x12,250x90x14,250x90x16" _
, DataOption:=xlSortNormal
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
With ActiveSheet.Sort
.SetRange Range("A4:G" & Cells(Rows.Count, "A").End(xlUp).Row)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Sort.SortFields.Clear
Application.ScreenUpdating = True
End Sub
In point of fact it is working but it is not comfortable for the other users to put some new data in custom list directly in VBA code (the list is not constant). So I want to create a new sheet with the list of items. Their order tells excel how to sort data in sheet "CZLON" (or some other). Something like this:
事实上，它正在工作，但其他用户在 VBA 代码中直接将一些新数据放入自定义列表中（列表不是恒定的）并不舒服。所以我想用项目列表创建一个新工作表。他们的顺序告诉 excel 如何对工作表“CZLON”（或其他一些）中的数据进行排序。像这样的东西：
Can you please help me with this issue?
你能帮我解决这个问题吗？
回答by Michael
Try this:
尝试这个：
CustomOrder:=SortItems
And add the following function to your module:
并将以下函数添加到您的模块中：
Function sortItems() As String
Dim arrSort() As Variant
Dim rngSort As Range
Set rngSort = Worksheets("Sheet1").Range("A1").CurrentRegion 'Change sheet name and range if needed
ReDim arrSort(1 To rngSort.Rows.Count)
For i = 1 To UBound(arrSort)
arrSort(i) = rngSort(i, 1)
Next
sortItems = Join(arrSort, ",")
End Function
If your list of items has a header, start the icounter at 2
如果您的项目列表有标题，则从2开始i计数器
