Unable to know why getting "Run Time Error 1004" on Sorting of the Data

Question

I am sorting Data in Column AT to AX and for that written following Code. But stuck at line ".Apply". I cannot understand what mistake I made? Please suggest solution.

Sub Yearly2_5DataArrangeOldestToNewest()
    Dim shDayData As Worksheet
    Set shDayData = ThisWorkbook.Worksheets("DayData")

    shDayData.Range("AT:AX").Select
    shDayData.Sort.SortFields.Clear
    shDayData.Sort.SortFields.Add Key:=Range("AT2:AX3610"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

    With shDayData.Sort
        .SetRange Range("AT1:AX3610")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply 'HERE IS PROBLEM
    End With

    shDayData.Range("AT:AX").Select
    Selection.NumberFormat = "[$-14009]dd/mm/yy;@"
    shDayData.Range("AX2").Select
End Sub

You've added 5 columns as a sort field - that doesn't really make sense. — Rory, May 11 '21 at 12:04
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). • Make sure there is no `Range` without a sheet specified. Change `Range("AT2:AX3610")` into `shDayData.Range("AT2:AX3610")` you have it 2 times. — Pᴇʜ, May 11 '21 at 12:11

score 1 · Answer 1 · answered May 11 '21 at 12:40

Just to expand on Rory's and Pᴇʜ's comments, you need to be careful about how you define the sort range and key ranges, and if you want to sort on all five columns, you need to define each column as a separate key.

Sub Yearly2_5DataArrangeOldestToNewest()
    
    Dim dataRange As Range
    Dim keyRange(1 to 5) As Range
    
    With ThisWorkbook.Worksheets("DayData")
        
        Set dataRange = .Range("AT2:AX360")
        Set keyRange(1) = .Range("AT3:AT360")
        Set keyRange(2) = .Range("AU3:AU360")
        Set keyRange(3) = .Range("AV3:AV360")
        Set keyRange(4) = .Range("AW3:AW360")
        Set keyRange(5) = .Range("AX3:AX360")
        
        With .Sort
            
            With .SortFields
                .Clear
                .Add2 Key:=keyRange(1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .Add2 Key:=keyRange(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .Add2 Key:=keyRange(3), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .Add2 Key:=keyRange(4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                .Add2 Key:=keyRange(5), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            End With '.SortFields
            
            .SetRange dataRange
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        
        End With '.Sort
    
    End With 'ThisWorkbook.Worksheets("DayData")

End Sub

Unable to know why getting "Run Time Error 1004" on Sorting of the Data

1 Answers1