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.
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!
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
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.
Code:
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!
Code:
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