Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Macro Loop
I have recorded a macro and am playing with it in Visual Basic (excel 2003).
I copy data to a new sheet with values only and now I want to sort all the columns individually to get the valid data to the top and spaces to the bottom. My macro can do this, coded with the following sort commands but i think it would be more efficient to run it as a loop condition. My columns will alway start in Columns("A:A").Select. Can i do something like Range (Selection, Selection.End(xlToRight)).Select -- starting in column A then pass this value to the loop variable and sort from column A to the end of that variable column. example body of sort commands to turn into loop procedure Columns("A:A").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("C:C").Select Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("D:D").Select Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal appreciate any ideas, Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Macro Loop
All similar but glad to help.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Sub sortINDIVIDUALcolumns() For i = 1 To Cells(1, Columns.Count).End(xlToLeft).Column Columns(i).Sort Key1:=Cells(2, i), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 'MsgBox i Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "anduare2" wrote in message ... I have recorded a macro and am playing with it in Visual Basic (excel 2003). I copy data to a new sheet with values only and now I want to sort all the columns individually to get the valid data to the top and spaces to the bottom. My macro can do this, coded with the following sort commands but i think it would be more efficient to run it as a loop condition. My columns will alway start in Columns("A:A").Select. Can i do something like Range (Selection, Selection.End(xlToRight)).Select -- starting in column A then pass this value to the loop variable and sort from column A to the end of that variable column. example body of sort commands to turn into loop procedure Columns("A:A").Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("B:B").Select Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("C:C").Select Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Columns("D:D").Select Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal appreciate any ideas, Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Macro Loop | Excel Programming | |||
Sorting Macro Loop | Excel Programming | |||
Sorting Macro Loop | Excel Programming | |||
Sorting a Loop | Excel Programming | |||
Do until loop with use of another macro in loop | Excel Programming |