Runtime Error 1004: “Sort method of Range class failed” (Key parameter errors).

VBA raises Runtime Error 1004: “Sort method of Range class failed” when the arguments provided to the .Sort method—specifically the Key ranges, sorting fields, or orientation settings—conflict with the dimensions, location, or worksheet scope of the range being sorted.

Quick Answer

Ensure the Key1 parameter points to a range on the same worksheet as the primary sort range, that Key1 refers to a single column (or row) within that boundary, that the sheet is unprotected, and that your code does not attempt to sort across merged cells. Fully qualify all range references to avoid sheet mismatches.

---

Why the Sort Method Fails

Excel provides two ways to sort data in VBA:

1. The classic Range.Sort method (common in older macros and concise scripts).
2. The modern Worksheet.Sort object model (which uses .SortFields.Add).

Both methods trigger this specific 1004 error when their parameters do not align with the dataset layout.

' Typical breaking line:
wsData.Range("A2:D50").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo

In the line above, if wsData is not the active sheet, Range("A2") defaults to ActiveSheet.Range("A2"). Because Key1 belongs to ActiveSheet while the sort range belongs to wsData, Excel aborts with Sort method of Range class failed.

---

Key Parameter Requirements and Fixes

1. Scope and Qualify the Key Range

The sort key must explicitly belong to the worksheet containing the data.

Sub SafeRangeSort()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Inventory")

    Dim dataRange As Range
    Set dataRange = ws.Range("A1:E100")

    ' Fully qualify Key1 with the worksheet reference:
    dataRange.Sort Key1:=ws.Range("B1"), _
                   Order1:=xlAscending, _
                   Header:=xlYes, _
                   MatchCase:=False, _
                   Orientation:=xlSortRows
End Sub

2. The Modern SortFields Alternative

For Excel 2007 and newer, the Worksheet.Sort object model provides granular sorting, but requires clearing previous sort keys first. If old sort keys remain active, adding a new sort key can create conflicts that trigger Error 1004.

Sub ModernTableSort()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Inventory")

    Dim sortRange As Range
    Set sortRange = ws.Range("A1:E100")

    With ws.Sort
        .SortFields.Clear ' Always clear old fields first
        .SortFields.Add Key:=ws.Range("B2:B100"), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
        .SetRange sortRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

3. Resolve Merged Cells Inside the Sort Range

Excel cannot sort any range containing merged cells. If your table contains even one merged row or column header that spills into the data rows, the sort engine fails.

• Fix: Unmerge cells across the target area:

ws.Range("A1:E100").UnMerge

If merged cells present structural issues elsewhere in your workbook, refer to Runtime Error 1004: “The information cannot be pasted because the copy area and the paste area are not the same size.”.

4. Matching Sort Key Lengths in SortFields

When using ws.Sort.SortFields.Add, the Key range must have the exact same vertical or horizontal span as the .SetRange parameter (excluding headers when configured):

• If .SetRange is ws.Range("A1:D50") with .Header = xlYes, your data span is rows 2 through 50.
• Your .SortFields.Add Key:=... should reference rows 2 through 50 (e.g., ws.Range("A2:A50")), not the whole sheet column (ws.Range("A:A")).

---

Diagnostic Summary Table

Error Message Variant	Root Cause	Primary Fix
“…cell or chart… on a protected sheet”	Cell Locked = True while worksheet protection is active.	Use ws.Protect UserInterfaceOnly:=True, or call ws.Unprotect before editing.
“Cannot access the file…”	Incorrect path string, network drop, or file is locked by a ghost process.	Validate with Dir(path) <> "", verify backslashes, and check for ~$ lock files.
“Sort method of Range class failed”	Key1 sheet scope mismatch, leftover sort fields, or merged cells.	Fully qualify Key1 to match the data sheet; clear existing SortFields.

---

Next Diagnostic Steps

If your sorting or file management routines run as part of a larger reporting workflow, verify that your macros do not attempt actions on hidden tabs by reviewing Runtime Error 1004: Why you can’t select a range on a Hidden Sheet.. For issues opening files programmatically, see Runtime Error 1004: “Method ‘Open’ of object ‘Workbooks’ failed.”. To handle unpredictable file and sort conditions gracefully at runtime, consider setting up a centralized error trap using Using On Error Resume Next vs. On Error GoTo 0 (The right way)..