| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting left to right programatically
I can record the following macro...
Columns("AY:BE").Select Selection.Sort Key1:=Range("AY1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal but I need the starting and end points of the sort to vary. When I assign variables to the... AY = colS BE = colE last row = endrow Range(Cells(1, colS), Cells(colE, EndRow)).Sort Key1:=Sheets(1).Range(Cells(1, colS)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal When this runs i get a Run Time Error 1004... so something's not pointing to the right cell... what am I doing wrong? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting left to right programatically
Range(Cells(1, colS), Cells(colE, EndRow)).Sort _
Key1:=Sheets(1).Cells(1, colS), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight, DataOption1:=xlSortNormal -- __________________________________ HTH Bob "jayklmno" wrote in message ... I can record the following macro... Columns("AY:BE").Select Selection.Sort Key1:=Range("AY1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal but I need the starting and end points of the sort to vary. When I assign variables to the... AY = colS BE = colE last row = endrow Range(Cells(1, colS), Cells(colE, EndRow)).Sort Key1:=Sheets(1).Range(Cells(1, colS)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal When this runs i get a Run Time Error 1004... so something's not pointing to the right cell... what am I doing wrong? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting left to right programatically
Try this:
colS = Columns("AY").Column colE = Columns("BE").Column EndRow = Range("AY65536").End(xlUp).Row -- CFS "jayklmno" wrote: I can record the following macro... Columns("AY:BE").Select Selection.Sort Key1:=Range("AY1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal but I need the starting and end points of the sort to vary. When I assign variables to the... AY = colS BE = colE last row = endrow Range(Cells(1, colS), Cells(colE, EndRow)).Sort Key1:=Sheets(1).Range(Cells(1, colS)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal When this runs i get a Run Time Error 1004... so something's not pointing to the right cell... what am I doing wrong? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting left to right programatically
hi
if everything is properly dim'ed then change this line Range(Cells(1, colS), Cells(colE, EndRow)).Sort to Range(Cells(1, colS), Cells(EndRow, colE)).Sort should work then regards FSt1 "jayklmno" wrote: I can record the following macro... Columns("AY:BE").Select Selection.Sort Key1:=Range("AY1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal but I need the starting and end points of the sort to vary. When I assign variables to the... AY = colS BE = colE last row = endrow Range(Cells(1, colS), Cells(colE, EndRow)).Sort Key1:=Sheets(1).Range(Cells(1, colS)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal When this runs i get a Run Time Error 1004... so something's not pointing to the right cell... what am I doing wrong? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Sorting left to right programatically
That did it... thanks! Note to self... row then column...
"FSt1" wrote: hi if everything is properly dim'ed then change this line Range(Cells(1, colS), Cells(colE, EndRow)).Sort to Range(Cells(1, colS), Cells(EndRow, colE)).Sort should work then regards FSt1 "jayklmno" wrote: I can record the following macro... Columns("AY:BE").Select Selection.Sort Key1:=Range("AY1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal but I need the starting and end points of the sort to vary. When I assign variables to the... AY = colS BE = colE last row = endrow Range(Cells(1, colS), Cells(colE, EndRow)).Sort Key1:=Sheets(1).Range(Cells(1, colS)), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal When this runs i get a Run Time Error 1004... so something's not pointing to the right cell... what am I doing wrong? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorting left to right | Excel Programming | |||
| sorting contents from right to left | Excel Worksheet Functions | |||
| Sorting Right to Left | Excel Worksheet Functions | |||
| programatically color fonts and auto sorting | Excel Programming | |||
| Sorting in reverse (from right-to-left and from top-to-bottom) | Excel Discussion (Misc queries) | |||
Hybrid Mode
