VBA & Macro Problems

Fix Excel VBA Run-Time Error 1004: Sorting with Variable Data Locations

Olivia Miller Sep 28, 2026 869 views

Question details

The user needs to sort columns A through F using an Excel VBA macro where the starting row is variable, but is encountering run-time error 1004.

How to Fix Excel VBA Sorting with Variable Data Locations (Error 1004)

Product: Microsoft Excel
Device & OS: not provided
Scenario: Writing a VBA macro to sort dynamic data ranges where the starting row changes based on the dataset size.
Observed behavior: The macro throws run-time error 1004 because it fails to properly identify and set the variable sort range and keys.

Before you start

Before modifying your macro code, ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in your ribbon.

Solution 1Recommended

Define Dynamic Ranges Using Find and CurrentRegion

Locate the specific header text dynamically and use CurrentRegion to set the exact sort range and keys, which prevents error 1004 caused by invalid range references.

Run-time error 1004 often occurs when VBA cannot resolve the range you are trying to sort. Relying on the ActiveCell property is risky because the active cell changes based on user clicks.

To fix this, write code that explicitly searches for your data's starting point (like a specific header) and builds the sort range dynamically using CurrentRegion and Resize.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications editor, then locate your macro module.

Declare Range Variables

Add variables for your entire data range and your specific sort keys at the top of your macro (e.g., Dim EntireRange As Range, SortKey1 As Range).

Find the Starting Cell

Use the Range.Find method to locate your data block dynamically. For example: Set StartCell = Worksheets("Summary").Cells.Find("Low Net Winner(s)").

Set the Dynamic Sort Range

Define your sorting block by referencing the found cell's region and resizing it to your column count: Set EntireRange = StartCell.CurrentRegion.Resize(, 6).

Define Sort Keys with Offset

Set your sorting keys dynamically relative to the StartCell. For example: Set SortKey1 = StartCell.Offset(0, 3).Resize(EntireRange.Rows.Count).

Apply the Sort Settings

Use a With block for Worksheets("Summary").Sort. Call .SortFields.Clear to remove old rules, add your new fields using .SortFields.Add Key:=SortKey1, apply .SetRange EntireRange, and finally call .Apply.

Define Dynamic Ranges Using Find and CurrentRegion

Avoid ActiveCell Dependency: Unless you can guarantee the user has clicked the exact correct cell before running the macro, always use explicit worksheet references and the Find method instead of ActiveCell.

Use WPS Spreadsheet for VBA Macros

Run VBA Macros Smoothly in WPS Spreadsheet

WPS Office provides robust support for VBA macros. You can edit, debug, and execute your dynamic sorting scripts seamlessly in WPS Spreadsheet, enjoying full compatibility with your existing Excel macros.

1. Download and Install: Download and install the latest version of WPS Office.
2. Open Your Workbook: Launch WPS Spreadsheet and open your Excel macro-enabled workbook (.xlsm).
3. Access Developer Tools: Navigate to the 'Developer' tab on the top ribbon.
4. Run Your Macro: Click on 'Macros' or open the 'Visual Basic Editor' to view, debug, and run your dynamic sorting code directly.

Fully compatible with Excel .xlsm and .xlsb macro formatsBuilt-in VBA editor for writing and debugging codeLightweight and fast data processing for large dynamic ranges

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get run-time error 1004 when sorting via VBA?

Error 1004 typically occurs when the VBA code references an invalid range, attempts to interact with a protected sheet, or tries to apply a sort to a worksheet that is not currently active while relying on the ActiveCell object.

How do I clear previous sort fields in an Excel macro?

Before applying a new sort, always include the command .SortFields.Clear within your Sort With block. This ensures no lingering sort rules conflict with the new criteria.

What does CurrentRegion do in Excel VBA?

The CurrentRegion property returns a range bounded by any combination of blank rows and blank columns. It is highly useful for dynamically selecting a contiguous block of data whose row or column count might change.

Excel & Data Problems