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


On 25/01/2012 11:59 PM, XR8 Sprintless wrote:
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