Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting columns of data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting columns of data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting data in columns | Excel Programming | |||
Sorting data in columns | Excel Worksheet Functions | |||
Sorting Data into columns without replacing the columns with data | New Users to Excel | |||
sorting data in columns | Excel Discussion (Misc queries) | |||
Sorting two columns of data | Excel Programming |