Sorting with variable number of sort fields
 

Hi

Anyone come up with an efficient way of sorting data based on variable
number of sort fields at runtime?

For example, if end-user wants to sort data on column 1, code is
straighttforward:
myRange.Sort key1:=.Cells(1,1)

However, if end-user can sort on upto 2 columns, say column x, and y, code
gets clunky:
if (user specified column x only) then
myRange.Sort key1:=.Cells(1,x)
else if (user specified column x and y) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y)

Now if end-user can sort on upto 3 columns, say x, y, z the code gets even
more clunky.
if (user specified column x only) then
myRange.Sort key1:=.Cells(1,x)
else if (user specified column x and y) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y)
else if (user specified columnx and y and z) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y), key3=Cells(1,z)

Is there a better solution?
Thanks

Sorting with variable number of sort fields
 

Here is a possible work around:

Sub Macro1()
Dim v1 As Variant, v2 As Variant
Dim v3 As Variant
v1 = ""
v2 = ""
v3 = ""

Set v1 = Range("C1")

Range("A1:F28").Sort Key1:=v1, Order1:=xlAscending, _
Key2:=v2, Order2:=xlAscending, _
Key3:=v3, Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

Leave V2 and V3 or just V3 as equal to "" if you don't want to sort on them.
Otherwise set them to a range.

--
Regards,
Tom Ogilvy

"BW" wrote in message
...
Hi

Anyone come up with an efficient way of sorting data based on variable
number of sort fields at runtime?

For example, if end-user wants to sort data on column 1, code is
straighttforward:
myRange.Sort key1:=.Cells(1,1)

However, if end-user can sort on upto 2 columns, say column x, and y, code
gets clunky:
if (user specified column x only) then
myRange.Sort key1:=.Cells(1,x)
else if (user specified column x and y) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y)

Now if end-user can sort on upto 3 columns, say x, y, z the code gets
even
more clunky.
if (user specified column x only) then
myRange.Sort key1:=.Cells(1,x)
else if (user specified column x and y) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y)
else if (user specified columnx and y and z) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y), key3=Cells(1,z)

Is there a better solution?
Thanks

Sorting with variable number of sort fields
 

Thanks Tom, that works great!

"Tom Ogilvy" wrote:

Here is a possible work around:

Sub Macro1()
Dim v1 As Variant, v2 As Variant
Dim v3 As Variant
v1 = ""
v2 = ""
v3 = ""

Set v1 = Range("C1")

Range("A1:F28").Sort Key1:=v1, Order1:=xlAscending, _
Key2:=v2, Order2:=xlAscending, _
Key3:=v3, Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub

Leave V2 and V3 or just V3 as equal to "" if you don't want to sort on them.
Otherwise set them to a range.

--
Regards,
Tom Ogilvy

"BW" wrote in message
...
Hi

Anyone come up with an efficient way of sorting data based on variable
number of sort fields at runtime?

For example, if end-user wants to sort data on column 1, code is
straighttforward:
myRange.Sort key1:=.Cells(1,1)

However, if end-user can sort on upto 2 columns, say column x, and y, code
gets clunky:
if (user specified column x only) then
myRange.Sort key1:=.Cells(1,x)
else if (user specified column x and y) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y)

Now if end-user can sort on upto 3 columns, say x, y, z the code gets
even
more clunky.
if (user specified column x only) then
myRange.Sort key1:=.Cells(1,x)
else if (user specified column x and y) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y)
else if (user specified columnx and y and z) then
myRange.Sort key1:=Cells(1,x), key2:=Cells(1,y), key3=Cells(1,z)

Is there a better solution?
Thanks