Sort All Columns in Excel 2003 with a Macro?

[TechTank]

Hi All,

I want to sort all columns on a sheet with a header row (In Row A1) in ascending order. The columns should all be sorted individually and not by any other cells. I have been using code similar to this:

    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

This does work for each column I specify in the macro but I'd like to extend the range to the last column and the last row if possible so I don't have to add all the columns in by hand.

Any help is greatly appreciated.

Thank you for your time,

Mark.

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim Col As Range
    For Each Col In ActiveSheet.UsedRange.Columns
        Col.Sort Key1:=Col.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    Next Col
End Sub

 

[TechTank]

Hi Andrew,

That code is absolutely spot on!!! Thank you so much for a quick and fantastic response.

I'm learning every time I ask a question on here but hope to help someone out one day with the solutions people post for mine.

Warm regards and thanks again.