VBA Sort for varying dimensions

[Derek Johansen]

Hey everyone...

I have a spreadsheet that is the result of a previous macro I have written,
and I now in another macro I essentially want to format this sheet. The
sheet is always going to have a header in the first row and be varying in
length.

I want to sort the column in descending order by column C, ignoring the
header in the top row. My previous attempts have failed! I tried
pre-recording macro, but the sort dimensions are absolute, and I need mine to
be relative. I tried entering the range as "Range(Cells(2, 3),
Cells(last_row, 3))" where last_row is defined previously as the last used
row in the sheet.

Anyone with tips would be very much appreciated!

Thanks for the help,

Derek

 

[Dave Peterson]

Sort has a Header parm that you can use.

And maybe you could sort the entire column:

with activesheet.range("C:C")
.Sort Key1:=.columns(1), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

or...

Avoiding row 1 headers (data starts in row 2)

Dim myRng as range
with activesheet
set myrng = .range("C2",.cells(.rows.count,"C").end(xlup))
end with

with myrng
.Sort Key1:=.columns(1), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

======
And you only wanted to sort that single column?????

Say you wanted to sort A:E by column C, but row 1 had headers:

Dim myRng as range
with activesheet
'lastrow determined by column A
set myrng = .range("A2:E" & .cells(.rows.count,"A").end(xlup).Row)
end with

with myrng
.Sort Key1:=.columns(3), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with

or header:=xlyes

Dim myRng as range
with activesheet
'lastrow determined by column A
set myrng = .range("A1:E" & .cells(.rows.count,"A").end(xlup).Row)
end with

with myrng
.Sort Key1:=.columns(3), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
end with


======
If you know your data, it's always best to specify that header parm -- don't let
excel guess.