Range.Sort in VBA
 

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

Range.Sort in VBA
 

Try A:X instead of A1:X.

Jeff

"Martin Los" wrote in message
...
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

Range.Sort in VBA
 

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


Thanks Jeff!! It works great!

-----Original Message-----
Try A:X instead of A1:X.

Jeff

"Martin Los" wrote
in message
...
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


.