Sort on 3 keys

[Guest]

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

 

[Jim Cone]

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"
<[email protected]>
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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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,