excel VBA sort, many headaches

Does anyone know how to replace "xlAscending" with a variable without generating an error. Or achieve the same flexibility another way. The sort order (Ascending or Descending) must depend on the value of key1. No case statements. Thank you for any advice.

matt

Selection.sort Key1:=Range ("C2"),Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Hi (again)

You can substitute the value of xlAscending (1) or xlDescending (2) - with a value held in a cell... In this case the value of B1 can be set to 1 or 2 and thus change the order of the sort.

Selection.Sort Key1:=Range("A1"), Order1:=Cells(1, 2).Value, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Thank you for the help!

Did you get this to work? I've been messing with it and can't get it.

Yes

If I understand right - what you are trying to do is dynamically change the sort order ...

try
Selection.Sort Key1:=Range("A1"), Order1:=1, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

for ascending

or

Selection.Sort Key1:=Range("A1"), Order1:=2, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

for descending.

You can find the values of constants by: "msgbox xlAscending"


Hope this helps

Perfect, exactly what I was looking for. I was maintaining a case statement with 20 cases since I couldn't dynamically change the sort order. How did you know that "msgbox xlascending" returned the integer value? Thats going to be very useful. Thanks!!