Macro to sort variable data range fails

[Kiwipingu]

Hi,

Can you please help me identify why this macro is failing with the
following error?

Runtime error 1004: Sort method of Range class failed

Row 7 is the header row
Data range has a variable number of rows (so using end(xldown) to
select the range)
Using column BA to select a contiguous range of cells as all cells are
populated in the column
Then using that selection to select entire rows in my list
Sorting as per criteria using row 7 as header row


Rows("7:7").Select
Range("BA7").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.EntireRow.Select
Selection.Sort Key1:=Range("BE8"), Order1:=xlAscending,
Key2:=Range("BB8" _
), Order2:=xlDescending, Key3:=Range("BM8"),
Order3:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=
_
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Range("BN8:BN9").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("BN9").Select

Thanks,
AS

 

[Jim Cone]

It worked for me in Xl 2003. I made sure there was data in all three of the columns.
I also reformatted the code, as your post omitted some line continuation characters "_".
'---
Rows("7:7").Select
Range("BA7").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.EntireRow.Select
Selection.Sort Key1:=Range("BE8"), Order1:=xlAscending, _
Key2:=Range("BB8"), Order2:=xlDescending, _
Key3:=Range("BM8"), Order3:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
Range("BN8:BN9").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("BN9").Select
'---
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html




"Kiwipingu" <[email protected]>
wrote in message
Hi,
Can you please help me identify why this macro is failing with the
following error?
Runtime error 1004: Sort method of Range class failed
Row 7 is the header row
Data range has a variable number of rows (so using end(xldown) to
select the range)
Using column BA to select a contiguous range of cells as all cells are
populated in the column
Then using that selection to select entire rows in my list
Sorting as per criteria using row 7 as header row

Rows("7:7").Select
Range("BA7").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.EntireRow.Select
Selection.Sort Key1:=Range("BE8"), Order1:=xlAscending,
Key2:=Range("BB8" _
), Order2:=xlDescending, Key3:=Range("BM8"),
Order3:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=
_
xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
Range("BN8:BN9").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("BN9").Select

Thanks,
AS