使用自定义排序进行自定义排序 [英] Custom sorting with custom ordering

问题描述

我想基于列G中的值对行进行排序.有3种可能的值:绿色，红色和黄色.我希望行的顶部依次是 Green ，然后是 Yellow ，然后是 Red .

I want to sort my rows based on the values in column G. There are 3 possible values: Green, Red and Yellow. I want the rows sorted with Green on top, then Yellow, then Red.

我尝试的所有结果均按字母顺序排序:绿色，红色然后是黄色.列R上有第二种排序方式，但工作正常.

Everything I try results in the sort order being alphabetical: Green, Red then Yellow. There is a secondary sort on column R, but that is working fine.

我的最新代码如下. rr是最后一行的编号.我已经尝试过使用Order1:=xlAscending和不使用Order1:=xlAscending的情况.

My latest code is below. rr is the number of the last row. I have tried it with and without Order1:=xlAscending.

sCustomList =绿色"黄色"红色"

sCustomList = "Green" "Yellow" "Red"

Application.AddCustomList ListArray:=sCustomList
    Range("A3:T" & rr).Sort Key1:=Range("G3:G" & rr), Order1:=xlAscending, _
    OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
    DataOption1:=xlSortNormal, Key2:=Range("R3:R" & rr), Order2:=xlAscending

推荐答案

如果使用SortFields对象，则不必引用自定义列表: 在何处更改各种范围参考应该很明显.除了G

If you use the SortFields object, you don't have to refer to custom lists: It should be obvious below where to change the various range references. I also added an alphabetical sort on one of the columns other than G

Option Explicit
Sub TrafficLightSort()
    Dim WS As Worksheet
    Dim rSortRange As Range, rSortKey As Range
    Const sSortOrder As String = "Green,Yellow,Red"

Set WS = Worksheets("sheet1")
With WS
    Set rSortRange = Range("E1", .Cells(.Rows.Count, "E").End(xlUp)).Resize(columnsize:=3)
    Set rSortKey = rSortRange.Columns(3)

    With .Sort.SortFields
        .Clear
        .Add Key:=rSortKey, _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            CustomOrder:=sSortOrder
        .Add Key:=rSortRange.Columns(1), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending
    End With

    With .Sort
        .SetRange rSortRange
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End With

End Sub

这篇关于使用自定义排序进行自定义排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！