how to sort 4 columns (fields) simultaneously 2010 Excel? 2

[feipezi]

Hi folks,

I tried to sort 4 fields at the same time but it's not happening. The result does not come up to what expected. Here is the code:


Sub Sort4Fields()
Cells(1, 1).CurrentRegion.Select
With Selection
.Sort Key1:=Range("a1"), Order1:=xlAscending, _
Key2:=Range("b1"), Order2:=xlAscending, _
Key3:=Range("c1"), Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
With Selection
.Sort Key1:=Range("d1"), Order1:=xlAscending, _
Key2:=Range("a1"), Order2:=xlAscending, _
Key3:=Range("b1"), Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub


Thnx in advance.

 

[PHV]

You want to sort by column D, then A, then B, then C ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

hi,

You must have Excel 97-2003.

first you sort the LEAST significant column.

Finally the MOST significant column.

For instance the way that an 80-column punch card deck (often called an IBM card) was sorted, was one column at a time, starting with the least significant column and proceeding to the most significant column.

So sort the three least significant and then the most significant column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Hi Skip,

You're right about sorting based on the importance of the fields but it will be quite indecisive to identify the fields by their importance.

No, I'm using 2010 Excel, not the 2003. I don't think you can sort more than 3 columns in one .Sort statement even with 2010: you will get a '1004'.

Here is the new solution and I think it works, which is consistent with what you specified.

Thanks.


Sub Sort4Fields()
Cells(1, 1).CurrentRegion.Select
With Selection
.Sort Key1:=Range("d1"), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
With Selection
.Sort Key1:=Range("a1"), Order1:=xlAscending, _
Key2:=Range("b1"), Order2:=xlAscending, _
Key3:=Range("c1"), Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub


If you don't care about Order (the default is Ascending, do the following:


Sub Sort4bFields()
With Range("A1").CurrentRegion
.Sort Key1:=Range("d1")
.Sort Key1:=Range("a1"), Key2:=Range("b1"), Key3:=Range("c1")
End With
End Sub

 

[feipezi]

I'm sorry PHV. I did not see your comments but I'm 100% with you. You can see my new code.

Thanks.

 

[SkipVought]

In 2010, you can have an unlimited number of sort columns. Just Macro Record setting it up.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[feipezi]

Skip,

Thanks for the tip again. You're right about the unlimited number of sorting columns for 2010. But old syntax will not work. I had to record a macro and then did some modification on it.

Thanks again.


Sub test()
Dim rng As Range
Set rng = Cells(1, 1).CurrentRegion
ActiveSheet.Sort.SortFields.Clear
With ActiveSheet.Sort.SortFields
.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.Add Key:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.Add Key:=Range("E1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Add Key:=Range("F1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
With ActiveSheet.Sort
.SetRange rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub