Is it possible to build sort statement?

[Norm]

I'd like to build a macro that does a number of user-specified things to a
file. One of them is to sort. The user would specify up to 3 columns to
sort. Can an Excel macro "build" the sort parameters? The following sure
doesn't work:

s = "Key1:=Range(""A2""), Order1:=xlAscending, " & _
"Key2:=Range(""B2""), Order2:=xlAscending, "
Range("A1:H6563").Sort s

Thanks for the help!

 

[Jacob Skaria]

Try this with 3 columns
Sub Macro1()
Range("A:C").Sort Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Key3:=Range("C2"), Order3:=xlAscending, _
Header:=xlGuess, Orientation:=xlTopToBottom
End Sub

If this post helps click Yes

 

[Jacob Skaria]

If you are looking at a statement no. Sort Method expects the below arguments
to be passed..all of which are optional..

expression.Sort(Key1, Order1, Key2, Type, Order2, Key3, Order3, Header,
OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2,
DataOption3)


If this post helps click Yes

 

[Tim Williams]

Your only option might be have a single sub with some optional parameters
and 3 separate .Sort calls depending on how many Keys (and sort directions)
get passed.
The SortRange, Key1 and Direction1 would be required, and the other keys and
directions optional.

Tim

 

[Patrick Molloy]

its perhaps easier than you'd think....
there must always be one param, plus optional several more...this code
allows up to 3


Option Explicit
Sub main() 'demo
UserSort "A"
UserSort "B", "C", "E"
End Sub
Sub UserSort(param1 As String, ParamArray addl() As Variant)
Select Case UBound(addl, 1)
Case -1
Range("A1").CurrentRegion.Sort Range(param1 & "1")
Case 0
Range("A1").CurrentRegion.Sort Range(param1 & "1"), , _
Key2:=Range(addl(0) & "1")
Case 1
Range("A1").CurrentRegion.Sort Range(param1 & "1"), , _
Key2:=Range(addl(0) & "1"), Key3:=Range(addl(1) &
"1")

End Select
End Sub