If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro to sort variable data range fails
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 |
#2
|
|||
|
|||
Macro to sort variable data range fails
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" 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 |
Thread Tools | |
Display Modes | |
|
|