VBA sort help

[alirulez]

Hi everyone

Im stuck on a VBA error and im pulling my hair out!

I get the error Runtime Error 1004 The sort reference is not valid. Make sure that its within the data you want to sort and the first Sort by box isnt the same or blank.

The error appears when executing this code. Im trying to sort a range from left to right based on two criteria
1) Row 50 descending and 2) Row 6 ascending.

    With ActiveSheet.Range("A1").CurrentRegion
        .Cells.Sort Key1:=.Rows(50), Order1:=xlDescending, _
                Key2:=.Rows(6), Order2:=xlAscending, _
                Orientation:=xlLeftToRight

Oddly enough this was working yesterday and I cannot fathom why it stopped working today.

Ive tried running the sort using the normal non-vba features, and this works fine. Ive even 'recorded a macro' based on running the sort using the excel GUI and I get this code - which doesnt error, but it simply doesnt sort properly!

Range("A1:BZ50").Select
    ActiveWorkbook.Worksheets("All Plans Available in County").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("All Plans Available in County").Sort.SortFields.Add _
        Key:=Range("A50:BZ50"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("All Plans Available in County").Sort.SortFields.Add _
        Key2:=Range("A6:BZ6"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("All Plans Available in County").Sort
        .SetRange Range("A1:BZ50")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
    End With

Im pretty sure there is nothing wrong with the data itself, because excel sorts it fine when I use its GUI. It just seems to either error when I use the first code, or simply not work when I use the second code (despite it being generated by the record a macro functionality!)

Any help would be immense!
A

 

[vcoolio]

Hello Alirulez,

I'm not sure why your code has suddenly failed you but here is another method that you could try:-

Sub SortStuff()

Dim ws As Worksheet: Set ws = ActiveSheet

ws.Rows(50).Sort ws.Cells(50, 1), 2
ws.Rows(6).Sort ws.Cells(6, 1), 1

End Sub

I hope that this helps.

Cheerio,
vcoolio.