Excel VBA Sort Method of Range Class Failed: Fixes

“Sort method of Range class failed” is usually Excel’s run-time error 1004: a VBA sort request could not be applied to the range as specified. The fix is to qualify the worksheet and sort range, use a key inside that range, set the header and orientation explicitly, and check for worksheet conditions such as protection or merged cells.

What the error means

Range.Sort is a method that asks Excel to reorder cells. The error does not identify one specific fault; it means Excel could not complete the requested sort. The line that fails is often the sort statement, but the cause may be a range reference built earlier, an incompatible worksheet layout, or a restriction on editing the cells.

This error is commonly reported as run-time error 1004. It can happen when a macro works on one sheet but fails on another because the second sheet has a different layout, protection, merged cells, or a different last row. A sort can also appear to run successfully while producing a bad result if the range leaves related columns behind. Therefore, check both whether the sort executes and whether entire records move together.

How Range.Sort works

The range before .Sort identifies the block Excel is allowed to reorder. The Key1 argument identifies the cells whose values determine the primary order. For a table-like block with names in column A and dates in column B, for example, the sort range might include both columns while the key points to the date cells in column B. Excel then moves the rows in the selected block according to those key values.

Arguments such as Order1, Header, and Orientation remove guesswork. xlAscending sorts from low to high or A to Z; xlDescending reverses that order. xlYes tells Excel that the first row of the selected range contains headers, while xlNo says it is data. xlTopToBottom sorts records by rows, which is the usual choice for a list. A wrong header setting can put a column label among the data or leave the first data row unsorted.

Check the common causes first

Before changing the macro, inspect the actual range and worksheet at the point where the error occurs. The most frequent sources are:

Unqualified references: Range("A1:D20") and Range("B2:B20") refer to the active sheet, which may not be the sheet you intend. If the sort range and key resolve to different sheets, the request is inconsistent.
A key that does not match the selected block: Make the key a column within the rows and columns being sorted. Check for a mistaken column letter, a different starting row, or an end row beyond the data.
Incorrect last-row logic: A calculation based on a column that has blanks can stop too soon. It may also include extra formatted or stray cells if the range is derived carelessly.
Header confusion: A header row included in the range needs Header:=xlYes. If the selected range starts at the first data row, use xlNo instead.
Sheet protection or merged cells: Excel may be unable to rearrange protected cells or a range containing merged cells. Check the sheet’s protection settings and whether any cells in the sort block are merged.
A non-contiguous selection: A range made of separate areas, such as A1:A10,C1:C10, is not a normal rectangular record block to sort. Use one continuous range that contains every field in each record.

Also confirm that the workbook and worksheet still exist under the names used by the macro. A reference to the wrong workbook can be just as misleading as a wrong sheet reference.

Use an explicit, qualified sort range

This example assumes a worksheet named Data, headers in row 1, records in columns A through D, and a value in column B on every record row. It sorts the complete rows by column B in ascending order. Save a copy of the workbook before running any macro: VBA changes cannot be undone with Ctrl+Z in Excel (or Command+Z on Mac).

Option Explicit

Sub SortDataByColumnB()
    ' Assumes row 1 has headers and column B is filled for every record.
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range
    Dim keyRange As Range

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    Set sortRange = ws.Range("A1:D" & lastRow)
    Set keyRange = ws.Range("B2:B" & lastRow)

    sortRange.Sort Key1:=keyRange, Order1:=xlAscending, _
        Header:=xlYes, Orientation:=xlTopToBottom
End Sub

The worksheet is stored in ws, and both ranges are built from that worksheet rather than whichever sheet happens to be active. The header is included in sortRange, but excluded from keyRange; the explicit Header:=xlYes tells Excel why. If column B can be blank for a valid record, calculate lastRow using another column that is reliably populated, or use a deliberate method that finds the last used record across the full data block.

The example assumes a plain worksheet range. If your data is an Excel table, see the table section below rather than applying this exact pattern without checking how the table is managed.

Verify headers, boundaries, and row integrity

A sort range should include every column that belongs to each record. If names are in A, dates in B, and amounts in C, sorting only column B can detach dates from their names and amounts. Select a rectangular range such as A1:C followed by the final data row, and use a key from within that block. If the sheet contains unrelated notes or another list beside the records, do not extend the sort range across those cells merely to make it larger.

Inspect how the range is constructed. For instance, if the header is in row 1 and data starts in row 2, a key beginning at row 1 can be valid only when the header treatment and range are consistent; excluding the header from the key makes the intention clearer. Check that the key and sort range have matching data-row boundaries. When diagnosing, temporarily display or inspect the range addresses in the VBA editor, or set a breakpoint on the sort line and examine the variables before it runs.

Sorting also reorders values; it does not repair inconsistent records, duplicate keys, or text that looks like a number but is stored as text. If values that appear equivalent sort unexpectedly, inspect their underlying types and contents. That is a data-quality issue rather than necessarily a failed sort.

Tables, filters, and protected sheets

Excel tables, created with Insert > Table or Ctrl+T on Windows, have their own structured range and sorting behavior. For table data, using the table’s sort interface or its VBA sort fields is often clearer than treating a changing table as a fixed address. A table can expand when rows are added, while a hard-coded range such as A1:D100 will not automatically describe later records. If a table sort fails, confirm that the key refers to a column in that same table and that the table’s header row is intact.

Filters do not by themselves mean a sort is impossible, but visible-row-only assumptions can lead to results different from what a user expects. Decide whether the macro should sort the full data set or operate on a filtered list, and test that behavior with the actual filter state. If a worksheet is protected, Excel may block sorting unless protection settings permit it. Unprotecting the sheet in code may be appropriate only when the macro is authorized to do so and any password is handled securely; do not hard-code or disclose a real password in a shared example.

Merged cells are another frequent obstacle because they do not form ordinary row-and-column records. Unmerge cells in the data block and repeat values where necessary before sorting. Also check for hidden columns or rows if the output looks incomplete: hiding data does not necessarily mean it is excluded from the sort.

Debug the failing line systematically

In the Visual Basic Editor, use Debug > Compile VBAProject to catch syntax and some declaration problems, then run the macro and choose Debug if the error dialog appears. The highlighted line is the point where Excel reports failure; it is not proof that every input to that line is valid. On Windows, Alt+F11 opens the Visual Basic Editor in many Excel installations. On Mac, Option+F11 commonly opens it; keyboard settings may require Fn as well.

Confirm the workbook and worksheet objects are the intended ones, and that the worksheet name is spelled exactly as it appears on its tab.
Inspect the sort range and key addresses, and verify that the key belongs to the same sheet and lies within the intended data block.
Check the row count, header row, and whether the key column can contain blanks that invalidate the last-row calculation.
Look for merged cells, sheet protection, separate selection areas, and table boundaries in the affected block.
Test with a small copy of representative data. This isolates layout problems without risking the original records.

For a repeatable macro, avoid relying on Select, Activate, or the user’s current selection. Directly qualifying the workbook, worksheet, range, and key makes the procedure more predictable. A single successful run on a clean sample is not enough if real worksheets contain different headers, blank rows, or protected regions.

When to choose another sorting approach

Range.Sort is suitable for straightforward one-key or multi-key sorts on a rectangular range. If you need more control, Excel’s Sort object lets VBA add sort fields and specify options such as sort-on values or cell color. For a structured Excel table, the table’s own sort object is generally a better fit because its data area can grow. For one-off work, the Data > Sort command may be simpler than a macro and lets you inspect the sort levels in the dialog.

These approaches do not remove the need to identify the correct records, header row, and sort key. Switching methods will not solve a protected sheet, invalid range, or merged-cell problem. Choose the method that matches the data structure, then validate a few records before relying on the result.

Frequently Asked Questions

What is run-time error 1004 in Excel VBA?
It is a general Excel/VBA error indicating that an operation could not be completed. With Range.Sort, it does not identify one cause; inspect the range, key, worksheet state, headers, and cell layout.

Does the sort key have to be inside the sort range?
For a normal record sort, use a key column that is part of the rectangular range being reordered. Keeping the key and sort block on the same worksheet and aligned to the same data rows avoids common reference problems.

Why does my macro sort the wrong worksheet?
Unqualified references such as Range("A1:D20") use the active sheet. Qualify every range through a worksheet variable, for example ws.Range(...), and set that variable to the intended workbook and sheet.

Can a macro sort a protected worksheet?
Only when the sheet’s protection settings allow the operation or the macro follows an authorized unprotect-and-reprotect process. Check the workbook’s protection design rather than assuming a sort statement can bypass it.

Can I undo a VBA sort with Ctrl+Z?
No. Excel’s normal Undo history is cleared when a macro runs, so Ctrl+Z on Windows and Command+Z on Mac will not reverse the sort. Save a separate copy before running the macro, or build a deliberate backup-and-restore process into the workbook.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.