Issue with Sorting a Range: Sort method of Range class failed

**helpmeinexcel** · 04-12-2013, 11:06 AM

Hi,

I am trying to sort a range of data that starts in Column A and goes to Column AT (including this column). The data I want to sort starts on the second row as I have the first row as a header. The last row of my data is constantly changing as rows get added. The last row of data will be in FirstBlankRow as that is the row that was just filled with the data via my macro.

Currently my code is adding my data to FirstBlankRow but it is not sorting the data afterwords. I keep getting Run-time error '1004': Sort method of Range class failed

With Sheets("Models")
         .Range(.Cells(2, 1), .Cells(FirstBlankRow + 1, 46)).Sort key1:=.Cells(2, 1), Key2:=.Cells(2, 4) ', Key3:=.Cells(2, 2)
         End With

Any help is greatly appreciated!

**AndyLitch** · 04-12-2013, 11:17 AM

Sub Sort()
Dim LastRow As Long

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    Range("A1:AT" & LastRow).Select
    ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MASTER").Sort.SortFields.Add Key:=Range("A2:A" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("MASTER").Sort
        .SetRange Range("A1:AT" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

Change the coloured text to suit
Red is the name of the worksheet
Blue is the sort column

**helpmeinexcel** · 04-12-2013, 01:51 PM

Thanks for your response, I was about to try it when I realized the sheet I was trying to sort was protected with a password. I unprotected the sheet and my original code works perfectly now.