VBA & Macro Problems

How to Fix Excel VBA Sorting Runtime Error 1004 with Dynamic Ranges

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to fix an Excel VBA macro that returns runtime error 1004 when attempting to sort dynamic data ranges across multiple specific columns.

Product: Microsoft Excel (VBA)
Device & OS: not provided
Scenario: Running a VBA macro to sort dynamic player data (such as 'Low Net' scores) based on multiple criteria columns (columns D, E, and F) where the exact row count varies.
Observed behavior: The sorting macro fails to execute and throws runtime error 1004 instead of properly sorting the dynamic data range.

Before you start

Verify that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and ensure the correct worksheet is active before running the VBA code.

Solution 1Recommended

Use Range Objects and Explicit Worksheet Qualification

Fix runtime error 1004 by explicitly qualifying the worksheet, avoiding string-based range names, and using the Resize property to build dynamic sort ranges.

Runtime Error 1004 often occurs in VBA sorting macros when the code cannot resolve string-based range names, or when worksheet references are ambiguous. Explicitly qualifying your worksheet and using robust Range objects ensures VBA knows exactly where the data resides.

Instead of hardcoding ranges as strings, use the Resize property to define the dynamic boundaries of your sorting area.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the VBA Editor and locate the module containing your sorting macro.

Qualify the Worksheet

Define the worksheet explicitly at the beginning of your subroutine using a With block or variable (e.g., Dim ws As Worksheet, Set ws = ThisWorkbook.Sheets("Sheet1")).

Build the Sort Range dynamically with Resize

Replace string range names with Range objects. Use the Resize property to capture dynamic rows based on your variables (e.g., Set mySortRange = ws.Range("A2:F2").Resize(dynamicRowCount)).

Add Sort Fields for Multiple Criteria

Clear any existing sort fields using ws.Sort.SortFields.Clear. Then, add new sort keys for columns 4, 5, and 6 (Columns D, E, and F) using the same dynamic range method.

Apply the Sort Object

Set your sort properties (such as Header, MatchCase, and Orientation), confirm the header range, and execute the command using ws.Sort.Apply.

Avoid Relying on the Active Cell: If your macro logic depends on ActiveCell, ensure the correct cell is selected before executing the code. A better practice is to rewrite the code to explicitly reference specific cells rather than relying on the active selection.

Advanced Sorting in WPS Spreadsheet

Sort Data and Manage Macros Easily with WPS Office

WPS Spreadsheet provides powerful advanced sorting features and robust macro support. If you are struggling with VBA runtime errors, you can quickly achieve complex multi-level sorts directly through the intuitive graphical interface, or use WPS Macro to run your corrected VBA code flawlessly.

1. Open your Workbook in WPS Spreadsheet: Launch WPS Office, open the Spreadsheet module, and load your .xlsm or .xlsx file.
2. Access the Custom Sort Tool: Highlight your data range (including headers), navigate to the 'Data' tab, and click on 'Sort' > 'Custom Sort'.
3. Add Multiple Sort Levels: In the Custom Sort dialog box, add columns D, E, and F by clicking 'Add Level'. Specify your preferred sorting order (e.g., Ascending or Descending) for each.
4. Apply the Sort Instantly: Click 'OK' to instantly sort your dynamic data based on multiple criteria, bypassing the need for complex VBA macro debugging.

Fully compatible with Microsoft Excel files and .xlsm macrosIntuitive Multi-level Sort interface supporting complex criteria without codingLightweight architecture with faster processing for large datasetsFree to download with a familiar and user-friendly interface

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get runtime error 1004 when sorting in Excel VBA?

Runtime error 1004 typically occurs when the VBA code refers to a range that doesn't exist, lacks proper explicit worksheet qualification, or relies on an incorrectly formatted string name for dynamic ranges.

How do I dynamically sort multiple columns using VBA?

You can dynamically sort by defining the data range using the Resize property. Clear the existing SortFields collection, then add each column's key (such as columns D, E, and F) sequentially before calling the .Apply method.

What does the Resize property do in Excel VBA?

The Resize property expands or shrinks the size of a specified range. When building a dynamic sort, you use it to expand a single row range downward to include all populated rows based on an integer variable.

Can I sort data without relying on ActiveCell in VBA?

Yes. It is highly recommended to avoid using ActiveCell. Instead, explicitly declare the Worksheet and Range objects (e.g., Worksheets("Sheet1").Range("A1:F100")) so your macro runs reliably regardless of which cell the user has selected.

Excel & Data Problems