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

Sorting Macro Loop
 

Thanks for the help guys, but this one is exactly what i am looking for. It
works great for what I was trying to do. Thanks again.

"Don Guillett" wrote:

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

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