Excel Error Codes

How to Fix Excel VBA Runtime Error 1004: Invalid Sort Reference

Emma Brown Oct 9, 2026 869 views

Question details

The user is encountering Runtime Error 1004 at the Sort.Apply statement when attempting to dynamically sort a range using a VBA macro.

How to Fix Excel VBA Runtime Error 1004: Invalid Sort Reference

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating data sorting dynamically using VBA scripts from the header row through the last data row.
Observed behavior: The VBA debugger stops at the .Apply statement, throwing a Runtime Error 1004 because the sort key and range are not properly qualified or do not successfully reference the same worksheet.

Before you start

Before modifying your VBA script, identify the exact worksheet containing the data you intend to sort and confirm which column dictates the last used row of your dataset.

Solution 1Recommended

Qualify Ranges and Define the Last Row Dynamically

Resolve the error by explicitly qualifying both the sort key and the sort range to ensure they reference the exact same worksheet, while dynamically calculating a valid last-row value.

Runtime Error 1004 during a sort operation almost always stems from unqualified range references. When ranges are not qualified with a specific worksheet, VBA defaults to the ActiveSheet, which may cause mismatches and trigger the error at the .Apply method.

Define the last row variable

Open the VBA Editor and declare a variable for the last row. Use a dynamic calculation such as 'LR = Cells(Rows.Count, 2).End(xlUp).Row' to find the last used row in column B.

Clear existing sort fields

Inside your With ActiveSheet.Sort block, use '.SortFields.Clear' to remove any prior sorting rules that might conflict with your new parameters.

Qualify the sort key

Set the sort key explicitly by adding '.SortFields.Add2 Key:=Range("B3")', ensuring it points to the correct header or starting cell.

Set the dynamic range

Apply the dynamically calculated last row to your sort range using '.SetRange Range("B3:K" & LR)'.

Apply the sort

Conclude your With block with '.Apply' to execute the sorted operation based on the fully qualified parameters.

Qualify Ranges and Define the Last Row Dynamically

Avoid Unqualified Ranges: If you are working with multiple worksheets in a macro, always replace 'ActiveSheet' and plain 'Range()' calls with explicit worksheet references like 'Worksheets("Data").Range()' to prevent unexpected errors.

Solution 2

Use Fixed Ranges to Isolate the Error

If dynamic ranges fail, temporarily hardcode the range to verify that the core sorting syntax and logic are correct.

Run Automated Macros Flawlessly in WPS Spreadsheet

WPS Spreadsheet offers robust support for VBA macros, allowing you to execute dynamic sorting scripts seamlessly. It is highly compatible with Microsoft Excel's VBA syntax, ensuring your automated tasks run smoothly without requiring major code rewrites.

1. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm or .xls).
2. Access developer tools: Navigate to the 'Developer' tab on the top ribbon menu to access macro features.
3. Edit your macro: Click on 'Macros', select your sorting script from the list, and click 'Edit' to open the VBA Editor.
4. Run the script: Ensure your dynamic ranges are properly formatted as instructed, then click 'Run' to seamlessly execute the sort.

Highly compatible with Microsoft Excel VBA macros and .xlsm formats.Lightweight and fast execution of automated sorting scripts.Built-in Developer tools for debugging and refining code easily.Free to download with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the VBA debugger specifically stop at the Sort.Apply line?

The '.Apply' method executes the sort operation using the parameters defined in the preceding lines. If the key or range references are invalid, unqualified, or do not match the active worksheet, the application cannot physically perform the sort, thereby throwing Error 1004 exactly at the execution line.

What does an 'unqualified range' mean in VBA?

An unqualified range (e.g., typing just 'Range("A1")' instead of 'Worksheets("Sheet1").Range("A1")') forces VBA to default to the currently active sheet. If your code intends to sort a background sheet but uses unqualified ranges, it will attempt to sort the wrong data and trigger an error.

How do I correctly find the last used row dynamically in VBA?

You can dynamically find the last used row by counting up from the very bottom of the sheet. For example, 'Cells(Rows.Count, 2).End(xlUp).Row' goes to the last possible row in column 2 (Column B) and jumps up to the first non-empty cell it encounters.

Do I always need to clear sort fields before defining new ones?

Yes, it is highly recommended to use '.SortFields.Clear' before defining new sort parameters. Failing to clear them can cause conflicts by stacking new sort conditions on top of previously applied ones, which often leads to unexpected behaviors or runtime errors.

Microsoft Office Error Codes