Build a Sort Dynamically

[Mitchbvi]

I am trying to work out how to build a sort when I do not know at the outset how many columns I need to sort on.

The first object was to see if I could pass the range and sort key dynamically. The following Macro does that getting range and column to sort by. When I try and build the entire sort string I get a message 1004. I included a MsgBox to make sure the sort string is correct and it seems to be.

Sub Range_Sort()
Dim SortT, SortS, SortRange, KeyCell1, KeyCell2, SortString
Call LastCell
Set SortRange = Range(Cells(1, 1), Cells(LROW, LCOL))
Set SortT = Application.InputBox("Select to sort", , Type:=8)
Set KeyCell1 = Range(SortT.Address)
'This Works
SortRange.Sort key1:=KeyCell1, Order1:=xlDescending
'This does not work
SortString = "key1:=KeyCell1, Order1:=xlDescending"
MsgBox SortString
SortRange.Sort SortString
End Sub

 

[Fluff]

Why are you trying to create a string for the sort when this line works?

SortRange.Sort key1:=KeyCell1, Order1:=xlDescending

 

[Mitchbvi]

Thank you for your repponse, I apologise I should have. made it clear, that at the outset I do not know how ,many columns I need to sort on. however I have solved the sort issue in the following macro. To me it still seems. clumsy however I have included it in case it is of interest to others.

Sub SortColumns()
'Sorts 1 or multiple Columns in ascending Order
Dim DataRange, SortCols, X, Y
Call LastCell
DataRange = Range(Cells(1, 1), Cells(LROW, LCOL))
ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
SortCols = Application.InputBox("Enter No columns to Sort")
X = 1
For Y = 1 To SortCols 'Adds Sort keys
KeyCell = KeyCell & X
Set KeyCell = Application.InputBox("Select Column " & X & " to sort", , Type:=8)
KeyCell = KeyCell.Address
ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range(KeyCell), Order:=xlAscending
X = X + 1
Next Y
With ActiveWorkbook.ActiveSheet.Sort
.SetRange Range("DataRange")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Fluff]

Glad you sorted it & thanks for letting us know.