Runtime Error 1004 sort function VBA

Question

I seem to be getting an error 1004 about half the time when I run this chunk of code and I'm not at all sure why:

Dim ranged As Range
Set ranged = Range("AJ2")
Set ranged = Range(ranged, ranged.End(xlDown))


Sheets(i).Select
ActiveWorkbook.Worksheets(i).Sort.SortFields.Add Key:=ranged, _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(i).Sort
    .SetRange ranged
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply

End With

The code is being run over a series of worksheets and the range in column AJ is different in size over each sheet and therefore the range definition at the start is necessary. The aim is to reverse the order of the range. Can anyone help with what the error is here?

score 0 · Answer 1 · edited May 23 '17 at 10:33

In most of the cases, you don't need to select the worksheet. You may want to see THIS
Do not use xlDown to construct your range. Use xlUp to do it by finding the last row which has data. You may want to see THIS

Combining both the above, your code can look like the below. Please try it. (UNTESTED)

Dim ranged As Range
Dim lRow As Long

'
'~~> Rest of the code
'

With ThisWorkbook.Sheets(i)
    '~~> Find Last row in Col AJ which has data
    lRow = .Range("AJ" & .Rows.Count).End(xlUp).Row

    '~~> Construct your range
    Set ranged = .Range("AJ2:AJ" & lRow)

    '~~> Sort
    .Sort.SortFields.Add Key:=ranged, _
                         SortOn:=xlSortOnValues, _
                         Order:=xlDescending, _
                         DataOption:=xlSortNormal
    With .Sort
        .SetRange ranged
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

'
'~~> Rest of the code
'

TRIED AND TESTED VERSION

Sub Sample()
    Dim ranged As Range
    Dim lRow As Long, i As Long

    For i = 1 To ThisWorkbook.Sheets.Count
        '
        '~~> Rest of the code
        '

        With ThisWorkbook.Sheets(i)
            '~~> Find Last row in Col AJ which has data
            lRow = .Range("AJ" & .Rows.Count).End(xlUp).Row

            '~~> Construct your range
            Set ranged = .Range("AJ2:AJ" & lRow)

            '~~> Sort
            .Sort.SortFields.Add Key:=ranged, _
                                 SortOn:=xlSortOnValues, _
                                 Order:=xlDescending, _
                                 DataOption:=xlSortNormal
            With .Sort
                .SetRange ranged
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With

        '
        '~~> Rest of the code
        '
    Next i
End Sub

Thanks for your help. I copied this code exactly into mine and it still comes up with "Run-Time Error 1004:The sort reference is not valid. Make sure that it's within the data that you want to sort, and the first Sort By box isn't the same or blank." What's really odd is that when I run it on one sheet only by changing With ThisWorkbook.Sheets(i) to With ThisWorkbook.Sheets("Sheetname") it works fine, but when I put it into the above structure to run over multiple ones as per your code it doesn't. Any ideas? — mm123, Nov 25 '14 at 03:54

Runtime Error 1004 sort function VBA

1 Answers1