Sorting columns of data

  • Thread starter Thread starter XR8 Sprintless
  • Start date Start date
X

XR8 Sprintless

Hi

I am trying to sort data like this

Name Field1 field2 field3 field4 field5
Joh 3 10 6 2 1
Bill 4 8 3 1 2
Fred 4 9 3 2 1
John 4 9 5 3 2
Adam 4 9 3 2 1

The end result should be
John 4 9 5 3 2
Adam 4 9 3 2 1
Fred 4 9 3 2 1
Bill 4 8 3 1 2
Joh 3 10 6 2 1

I want the field1 data to be sorted first then field2 then field3,
field4 then the name.

I tried this but get an error

Selection.Sort Key1:=Range("b45"), Order1:=xlDescending,
Key2:=Range("c45"), Order2:=xlDescending, Key3:=Range("d45"),
Order3:=xlDescending, Key4:=Range("e45"), Order4:=xlDescending,
Key5:=Range("a45"), Order5:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortTextAsNumbers

Using excel 2010.
Can anyone help?

Thanks
 
A little more searching and I found some code I was able to modify to work.

Dim rng As Range
Set rng = Range("a45:f45", Selection.End(xlDown))


Dim srt As Sort

Dim sht As Worksheet
Set sht = ActiveSheet

Set srt = sht.Sort

srt.SortFields.Clear
srt.SortFields.Add Key:=Range("B45"), _
SortOn:=xlSortOnValues, Order:=xlDescending
srt.SortFields.Add Key:=Range("C45"), _
SortOn:=xlSortOnValues, Order:=xlDescending
srt.SortFields.Add Key:=Range("D45"), _
SortOn:=xlSortOnValues, Order:=xlDescending
srt.SortFields.Add Key:=Range("E45"), _
SortOn:=xlSortOnValues, Order:=xlDescending
srt.SortFields.Add Key:=Range("A45"), _
SortOn:=xlSortOnValues, Order:=xlAscending

' Set the sort range:
srt.SetRange rng
srt.Header = xlNo
srt.MatchCase = True
' Apply the sort:
srt.Apply

Original code here

http://msdn.microsoft.com/en-us/library/hh128804.aspx
 
Back
Top