查看您的代码,sCustomList
看起来像一个字符串类型变量而不是变体数组。我在自定义排序列表方面的成功在于每次都创建一个新的排序列表并使用最高的索引号来引用它。
Sub custom_sort()
Dim vCustom_Sort As Variant, rr As Long
vCustom_Sort = Array("green", "yellow", "red", Chr(42))
Application.AddCustomList ListArray:=vCustom_Sort
With Worksheets("Sheet2") '<~~ set this properly!
.Sort.SortFields.Clear
rr = .Cells(Rows.Count, "G").End(xlUp).Row
With .Range("A3:T" & rr)
'use this to presort one or more secondary fields before the primary custom sort
'.Cells.Sort Key1:=.Columns(18), Order1:=xlAscending, _
Key2:=.Columns(1), Order2:=xlDescending, _
Orientation:=xlTopToBottom, Header:=xlYes
.Cells.Sort Key1:=.Columns(7), Order1:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlYes, MatchCase:=False, _
OrderCustom:=Application.CustomListCount + 1
End With
.Sort.SortFields.Clear
End With
End Sub
之间有一个转折.Cells.Sort.SortFields.Add
and .Cells.Sort
这通常会产生一些混乱。这.SortFields.Add 方法 https://msdn.microsoft.com/en-us/library/office/ff836827.aspx uses a CustomOrder:=
参数和范围排序方法 https://msdn.microsoft.com/en-us/library/office/ff840646.aspx uses a OrderCustom:=
范围。这两者绝对不一样,但经常互换使用,带来灾难性的结果。