Excel '07 macro breaks in '03: Multiple levels of sorting

**dinerroll** · 06-13-2011, 10:38 AM

Hello All,

I'm writing because I have a worksheet and macro that I use on a machine with Excel '07. I've been able to build all of the logic of the macro I inherited to be compatible with Excel 2003 except for this issue with multiple levels of sorting. The code that breaks in Excel 2003 is below... I get a runtime error that "object doesn't support this property or method)...any workarounds? They don't have to be pretty. This workbook is being used by 8 people who all work in the same room.

ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("A2:A407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("B2:B407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("D2:D407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("H2:H407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("E2:E407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("J2:J407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("O2:O407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("R2:R407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("S2:S407"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Data").Sort
        .SetRange Range("A1:X407")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

**romperstomper** · 06-13-2011, 10:42 AM

The Sort object was added in 2007. You'll need to use the Range.Sort method and do three sorts - first on O, R and S; then on H, E and J; and lastly on A, B and D.

**dinerroll** · 06-13-2011, 10:46 AM

Hey romperstomper. Thanks for the quick reply. I used to just used SQL to do most of my data analysis, so I really don't have any VBA experience. Can you give just a quick example of what the code for that would look like for one of the sorts?

**romperstomper** · 06-13-2011, 10:56 AM

For example:


   With Range("A1:X407")
      .Sort Key1:=Range("O2"), Order1:=xlAscending, _
            Key2:=Range("R2"), Order2:=xlAscending, _
            Key3:=Range("S2"), Order3:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
            DataOption3:=xlSortNormal
      .Sort Key1:=Range("H2"), Order1:=xlAscending, _
            Key2:=Range("E2"), Order2:=xlAscending, _
            Key3:=Range("J2"), Order3:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
            DataOption3:=xlSortNormal
      .Sort Key1:=Range("A2"), Order1:=xlAscending, _
            Key2:=Range("B2"), Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
            xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
            DataOption3:=xlSortNormal
   End With

**dinerroll** · 06-13-2011, 11:02 AM

Thank you so much dude. I came up with

Sub Macro1()
'
' Macro1 Macro
'

   Worksheets("Data").Range("A1:W3000").Sort _
        Key1:=Worksheets("Data").Range("O1"), _
        Key2:=Worksheets("Data").Range("R1"), _
        Key3:=Worksheets("Data").Range("S1")

   Worksheets("Data").Range("A1:W3000").Sort _
        Key1:=Worksheets("Data").Range("H1"), _
        Key2:=Worksheets("Data").Range("E1"), _
        Key3:=Worksheets("Data").Range("J1")

   Worksheets("Data").Range("A1:W3000").Sort _
        Key1:=Worksheets("Data").Range("A1"), _
        Key2:=Worksheets("Data").Range("B1"), _
        Key3:=Worksheets("Data").Range("D1")

'
End Sub

But your solution looks way better. Thank you so much for the original suggestion and this code.

**romperstomper** · 06-13-2011, 11:36 AM

Glad to help.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save