VBA Sort limited limited to 3?

[LNG2013]

I have not found any clear answer to this but is there a way to sort by more then 3 selections.

I want to sort by Date, Team, User, Item, then Sub iTEM

 

[LNG2013]

Just to clarify -

Below is the code I am using to sort... When adding a Key4 it fails

    Sub SortData()
    Range("A2:FR29921").Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("F2"), _
     Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, Key4:=Range("G2"), Order4:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
'Objective5m.Range("A1").AutoFilter
End Sub

 

[John Davis]

Just to clarify -

Below is the code I am using to sort... When adding a Key4 it fails

    Sub SortData()
    Range("A2:FR29921").Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("F2"), _
     Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, Key4:=Range("G2"), Order4:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
'Objective5m.Range("A1").AutoFilter
End Sub

I think 3 is the limit, does this help?

http://www.mrexcel.com/archive/Data/1401.html

Or you could just add another sort:

Sub LNG2013()

With  Range("A2:FR29921")
     .Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("F2"), _
     Order2:=xlAscending, Key3:=Range("D2"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
        xlSortNormal
        
       .Sort Key1:=Range("4th Key"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
        
End With

End Sub

 

[LNG2013]

Yeah I just ended up adding a second sort, but it is limited to 3, doesn't make much sense to have such a short limit but... adding another works ok.

 

[Jerry Sullivan]

Yeah I just ended up adding a second sort, but it is limited to 3, doesn't make much sense to have such a short limit but... adding another works ok.

What version of Excel are you using? Excel 2007 doesn't limit you to 3 keys.

 

[Jerry Sullivan]

If you are using xl2007 or later (or for anyone who finds this post through a search)

Use SortFields.Add Method instead of

.Sort Key1:=, Key2:=, Key3:=

You can find instuctions for SortFields.Add Method in VBA Help.

 

[John Davis]

If you are using xl2007 or later (or for anyone who finds this post through a search)

Use SortFields.Add Method instead of

.Sort Key1:=, Key2:=, Key3:=

You can find instuctions for SortFields.Add Method in VBA Help.

Thanks for the tip Jerry. I use xl2003 at work. I actually have xl2007 at home. I just don't use it that much, but it's good too know.

 

[pcc]

Another way:
Insert a helper column in column A
in this column, concatenate the values in the columns to be sorted, in the order that you want them sorted. - use a formula eg =B2&E2&D2&C2&G2, where the columns to be sorted are
B then E then D then C then G.
fill formula to end of data
copy, paste values in column A
sort by column A
delete column A

 

[LNG2013]

Just for reference I was on Excel 2003 which doesnt have Keys past 3

 

[P_Guru]

I did find this thread via a search, and I thank you all for your thorough answers.

But....

surely performing a second iteration of sort will, to an extent, override the first? So that, in practice Keys "4-6" are the primary keys, and "1-3" are the secondary keys.

Or have I missed something fundamental?