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

HI

Try this:

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

Regards,
Per

"asmithbcat" skrev i meddelelsen
...
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

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.
--
Gary''s Student - gsnu200906


"asmithbcat" wrote:

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