Sort by a Variable Range

[asmithbcat]

I have numerous worksheets that I would like to sort using a variable range.
I will always sort by the last column in ascending order.

I have included the following code in my Macro

Range(" & AllData.Address & ").Sort Key1:=Range(" & FormulaCell.Address &
"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

The AllData variable was defined as a range and selects all the data I would
like to sort. The FormulaCell variable is set as my sort key. Any thoughts
on why I would receive the following error.

Run-time error '1004': Method 'Range' of object '_Global' failed

 

[Per Jessen]

HI

Try this:

AllData.Sort Key1:=Range(FormulaCell.Address), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Regards,
Per

 

[Gary''s Student]

This assumes that you have a header row. It determines the last column and
set the sort key as appropriate:

Sub Macro1()
Dim AllData As Range, KeyRange As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set AllData = Range("A:L")
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
nLastColumn = AllData.Columns.Count + AllData.Column - 1
nFirstRow = AllData.Row
Set KeyRange = Cells(nFirstRow + 1, nLastColumn)
AllData.Sort Key1:=KeyRange, Order1:=xlAscending, Header:=xlYes
End Sub

Of course, you would set AllData to meet your needs.