Sorting via CustomOrder

[kalim]

Hi excel users.

I have vba code that sorts a table via 3 criteria (Year, Gender and last name). The problem is I am getting a 'compile error: defined name not found' message.
It seems not to like the CustomOrder1 code (causing the error).
The code works if I use the macro recorder to create the code, but when I tried to clean it up, I can't seem to quite get it to work.

What am I missing?
Thanks.

Sub sort()
With Range("table2")
 
   .Sort Key1:=Range("Table2[Year]"), Order1:=xlAscending, CustomOrder1:="K,1,2", _
    Key2:=Range("Table2[Gender]"), Order2:=xlAscending, _
    Key3:=Range("Table2[Last name]"), Order3:=xlAscending, _
    MatchCase:=False, Orientation:=xlTopToBottom, Header:=xlYes
 
    End With
 
 
End Sub

 

[Andrew Poulsom]

There isn't a CustomOrder1 argument. There is an OrderCustom argument which accepts a one-based integer offset to the list of custom sort orders.

 

[kalim]

Hi Andrew thanks for the reply.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Ok if there is no CustomOrder argument then why is it allowed (works) if you record the macro with the macro recorder?<o></o>
Surly it must be possible then?
<o></o>
Thanks.<o></o>

 

[Andrew Poulsom]

The macro recorder gives OrderCustom not CustomOrder.

 

[kalim]

Hi again Andrew, but I just checked again and the macro recorder does write the code using CustomOrder (excel 2007 and 2010) - maybe you are using an earler version of excel???

As I said before I cut most of it down, just not sure how to add the CustomOrder part of it.

Here is the exact code the recorder produces.

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveWorkbook.Worksheets("One").ListObjects("Table2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("One").ListObjects("Table2").Sort.SortFields.Add Key _
        :=Range("Table2[Year]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        CustomOrder:="K,1,2", DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("One").ListObjects("Table2").Sort.SortFields.Add Key _
        :=Range("Table2[Gender]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("One").ListObjects("Table2").Sort.SortFields.Add Key _
        :=Range("Table2[Last name]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("One").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks.

 

[Andrew Poulsom]

It uses CustomOrder with the Add method of the SortFields object. It doesn't use it with the Sort method of the Range object - that's OrderCustom. What's recorded has changed in Excel 2007, which uses the new Sort object. Here's what I get in Excel 2003:

    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=2, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

 

[kalim]

You are absolutely right Andrew thanks.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I got it to work for me.<o></o>