Thread: Macro - sort columns

Hello,

I wrote a Macro last night that worked and now it fails.

It's the final part of a Macro and I want to sort the data in just columns A:B (has header rows).
The number of rows will change depending on the dataset so I want it to be able to determine all rows (or to last row).

I recorded a code to begin with and am now editing it however when it gets to this part, I get "Run-time error '9' Subscript out of range"...

The code I have is:

Columns("A:B").Select
        ActiveWorkbook.Worksheets("Sheet 1").sort.SortFields. _
        Clear
        ActiveWorkbook.Worksheets("Sheet 1").sort.SortFields. _
        Add Key:=Range("A2").End(xlDown), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet 1").sort
        .SetRange Range("A:B")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Any help would be much appreciated.

Thank you in advance,

Damien

The code that you have is typical to excel 2007 onwards. You require the older version of the code that is used for 2003 (I assume it will also work for 2000). Code like this -

Cells.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("E2") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal

The subscript out of range error is usually associated with a tab name that does not match. Check if your sheet in the file is named as "Sheet 1" (with the space) or "Sheet1".

Please change your profile information to reflect 2007 thanks.