range selection for a sort on a excel db

[Janis]

I get an error on the Rows.Count line because I don't know how/what to select
for the range to sort on. Does it have to select the entire worksheet cells
in order to sort on the 3 keys or does it just need the top to bottom range
of 1 of the columns? Right now I selected the A1:

There are just 3 keys to sort on, Q1, S1, & A1 are the header fields. The
first two are ID numbers and A1 is alpha sort. This macro isn't sorting.

THANKS,


Sub Sort()
'
' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet
Set rng = .Range(.Cells(1, 1), .Cells(27, 1))
(Rows.Count, "A").End(xlUp)

rng.Sort key1:=Cells(17, 2), Order1:=xlAscending, _
key2:=Cells(19, 2), Order2:=xlDescending, _
key3:=Cells(1, 2), Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With


End Sub

 

[Jim Thomlinson]

You can try this. The only thing tha tI notice is taht you probably want to
specify whether there is a header row instead of xlGuess. Change it to xlYes
or xlNo.

Sub Sort()
'
' Sorts by Item Name, Dept, Status# Macro
Dim rng As Range

' sorts on Dept, & Status since there is only 3 keys available in a sort
With ActiveSheet
Set rng = .Range(.Cells(1, 1), (Rows.Count, 27).End(xlUp))

rng.Sort key1:=Cells(17, 2), Order1:=xlAscending, _
key2:=Cells(19, 2), Order2:=xlDescending, _
key3:=Cells(1, 2), Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End With

 

[Janis]

Thanks Jim, you answered this before but there were 4 sorts now there are
only 3 so it got changed and I couldn't figure out how to do the range,