Range.Sort in VBA

[Martin Los]

I recorded a sorting procedure with the macrorecorder:

Range("A1:X4661").Sort Key1:=Range("A2"),
Order1:=xlAscending, Key2:= _
Range("F2"), Order2:=xlDescending,
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

I want this to be flexible, so if I add rows, the macro
still works. So I have to get the hardcoding away in Range
("A1:X4661").Sort ... and get something like

Range("A1:X" & variableLngLastLine & ").Sort Key1:=Range
("A2"), Order1:=xlAscending, Key2:= _
Range("F2"), Order2:=xlDescending,
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

This doesn´t work however. Can anybody help me how to
softcode the hardcode macro in VBA?

TIA

MArtin

 

[Jeff Standen]

Try A:X instead of A1:X.

Jeff

I recorded a sorting procedure with the macrorecorder:

Range("A1:X4661").Sort Key1:=Range("A2"),
Order1:=xlAscending, Key2:= _
Range("F2"), Order2:=xlDescending,
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

I want this to be flexible, so if I add rows, the macro
still works. So I have to get the hardcoding away in Range
("A1:X4661").Sort ... and get something like

Range("A1:X" & variableLngLastLine & ").Sort Key1:=Range
("A2"), Order1:=xlAscending, Key2:= _
Range("F2"), Order2:=xlDescending,
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

This doesn´t work however. Can anybody help me how to
softcode the hardcode macro in VBA?

TIA

MArtin

 

[Guest]

I must have been thinking:
"Why do it the hard way if you can do it the simple way?"


Thanks Jeff!! It works great!