Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on 3 keys
the division header field is 15,1
dept header field is 16,1 status header field is 17,1 I want this to sort on all 3 in this order. The sort doesn't work thanks, Sub SortDivDept() 'sorts by Divsion, Department & Status Dim Rng As Range Set Rng = Range("A1:Q1") With ActiveSheet Rng.Sort key1:=Cells(15, 1), key2:=Cells(16, 1), key3:=Cells(17, 1), Orientation:=xlSortColumns, MatchCase:=False End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on 3 keys
You are sorting a single row. To the best of my knowledge multiple keys
are not allowed. Also, MS in its wisdom has assigned values to the constants as follows, so... xlLeftToRight = 2 xlTopToBottom = 1 while... xlSortRows = 2 xlSortColumns = 1 You need an orientation constant with a value of 2. Also, blanks sort to the bottom/right so sorting A1:Q1 is going to jam those three cells all the way to left if the other cells are blank. Note: "With ActiveSheet" is inoperative (no dots). See how this works for you... Sub SortDivDept() 'sorts by Division, Department & Status Dim Rng As Range Set Rng = ActiveSheet.Range("O1:Q1") Rng.Sort key1:=Rng, Orientation:=xlLeftToRight, MatchCase:=False End Sub '-------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Janis" wrote in message the division header field is 15,1 dept header field is 16,1 status header field is 17,1 I want this to sort on all 3 in this order. The sort doesn't work thanks, Sub SortDivDept() 'sorts by Divsion, Department & Status Dim Rng As Range Set Rng = Range("A1:Q1") With ActiveSheet Rng.Sort key1:=Cells(15, 1), key2:=Cells(16, 1), key3:=Cells(17, 1), Orientation:=xlSortColumns, MatchCase:=False End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on 3 keys
Ok you have lost me. There are up to 3 keys in a sort. Janis your original
code was fairly close except as Jim mentions you are trying to sort a single row. Your code should look something more like this... Sub SortDivDept() 'sorts by Divsion, Department & Status Dim Rng As Range With ActiveSheet 'Set Rng = .Range("A1:Q1") Set Rng = .range(.Range("Q1"), .Cells(rows.count, "A").end(xlup)) Rng.Sort key1:=.Cells(15, 1), _ key2:=.Cells(16, 1), _ key3:=.Cells(17, 1), _ MatchCase:=False, _ Header:=xlYes End With End Sub -- HTH... Jim Thomlinson "Jim Cone" wrote: You are sorting a single row. To the best of my knowledge multiple keys are not allowed. Also, MS in its wisdom has assigned values to the constants as follows, so... xlLeftToRight = 2 xlTopToBottom = 1 while... xlSortRows = 2 xlSortColumns = 1 You need an orientation constant with a value of 2. Also, blanks sort to the bottom/right so sorting A1:Q1 is going to jam those three cells all the way to left if the other cells are blank. Note: "With ActiveSheet" is inoperative (no dots). See how this works for you... Sub SortDivDept() 'sorts by Division, Department & Status Dim Rng As Range Set Rng = ActiveSheet.Range("O1:Q1") Rng.Sort key1:=Rng, Orientation:=xlLeftToRight, MatchCase:=False End Sub '-------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Janis" wrote in message the division header field is 15,1 dept header field is 16,1 status header field is 17,1 I want this to sort on all 3 in this order. The sort doesn't work thanks, Sub SortDivDept() 'sorts by Divsion, Department & Status Dim Rng As Range Set Rng = Range("A1:Q1") With ActiveSheet Rng.Sort key1:=Cells(15, 1), key2:=Cells(16, 1), key3:=Cells(17, 1), Orientation:=xlSortColumns, MatchCase:=False End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on 3 keys
Thanks, I actually needed 4 keys. The item name is only an alphabetical sort
so I took out the with block and copied it above to sort first on one key only. Then I repeated the with block again for the 3 keys below. Thanks again. "Jim Thomlinson" wrote: Ok you have lost me. There are up to 3 keys in a sort. Janis your original code was fairly close except as Jim mentions you are trying to sort a single row. Your code should look something more like this... Sub SortDivDept() 'sorts by Divsion, Department & Status Dim Rng As Range With ActiveSheet 'Set Rng = .Range("A1:Q1") Set Rng = .range(.Range("Q1"), .Cells(rows.count, "A").end(xlup)) Rng.Sort key1:=.Cells(15, 1), _ key2:=.Cells(16, 1), _ key3:=.Cells(17, 1), _ MatchCase:=False, _ Header:=xlYes End With End Sub -- HTH... Jim Thomlinson "Jim Cone" wrote: You are sorting a single row. To the best of my knowledge multiple keys are not allowed. Also, MS in its wisdom has assigned values to the constants as follows, so... xlLeftToRight = 2 xlTopToBottom = 1 while... xlSortRows = 2 xlSortColumns = 1 You need an orientation constant with a value of 2. Also, blanks sort to the bottom/right so sorting A1:Q1 is going to jam those three cells all the way to left if the other cells are blank. Note: "With ActiveSheet" is inoperative (no dots). See how this works for you... Sub SortDivDept() 'sorts by Division, Department & Status Dim Rng As Range Set Rng = ActiveSheet.Range("O1:Q1") Rng.Sort key1:=Rng, Orientation:=xlLeftToRight, MatchCase:=False End Sub '-------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Janis" wrote in message the division header field is 15,1 dept header field is 16,1 status header field is 17,1 I want this to sort on all 3 in this order. The sort doesn't work thanks, Sub SortDivDept() 'sorts by Divsion, Department & Status Dim Rng As Range Set Rng = Range("A1:Q1") With ActiveSheet Rng.Sort key1:=Cells(15, 1), key2:=Cells(16, 1), key3:=Cells(17, 1), Orientation:=xlSortColumns, MatchCase:=False End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort on 3 keys
"Jim Cone" wrote: You are sorting a single row. To the best of my knowledge multiple keys are not allowed. Also, MS in its wisdom has assigned values to the constants as follows, so... Not what I wanted to do. Thanks, xlLeftToRight = 2 xlTopToBottom = 1 while... xlSortRows = 2 xlSortColumns = 1 You need an orientation constant with a value of 2. Also, blanks sort to the bottom/right so sorting A1:Q1 is going to jam those three cells all the way to left if the other cells are blank. Note: "With ActiveSheet" is inoperative (no dots). See how this works for you... Sub SortDivDept() 'sorts by Division, Department & Status Dim Rng As Range Set Rng = ActiveSheet.Range("O1:Q1") Rng.Sort key1:=Rng, Orientation:=xlLeftToRight, MatchCase:=False End Sub '-------------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Janis" wrote in message the division header field is 15,1 dept header field is 16,1 status header field is 17,1 I want this to sort on all 3 in this order. The sort doesn't work thanks, Sub SortDivDept() 'sorts by Divsion, Department & Status Dim Rng As Range Set Rng = Range("A1:Q1") With ActiveSheet Rng.Sort key1:=Cells(15, 1), key2:=Cells(16, 1), key3:=Cells(17, 1), Orientation:=xlSortColumns, MatchCase:=False End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Many Sort Keys | New Users to Excel | |||
Array Sort on 2 keys | Excel Programming | |||
Need to know the Sort Column Keys | Excel Programming | |||
Sort using 4 keys | Excel Programming | |||
Dynamically set Sort keys | Excel Programming |