VBA & Macro Problems

How to Fix Excel VBA Run-Time Error 1004 with Merged Cells

Maira Mehtab Sep 28, 2026 869 views

Question details

A user is experiencing Run-time error 1004 when executing an Excel invoice macro to clear fields, likely caused by modifying the column layout and interacting with merged cells.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running a VBA macro to clear fields and save data for a new invoice after rearranging worksheet columns.
Observed behavior: The macro halts execution and displays a Run-time error 1004, preventing the invoice fields from being cleared and revealing an unassigned variable in the code.

Before you start

Before modifying your VBA code or altering the worksheet layout, ensure you create a duplicate copy of your Excel workbook so you can safely test changes without losing your original data.

Solution 1Recommended

Address Merged Cells in the Target Range

Run-time error 1004 frequently occurs when a VBA script attempts to clear a single cell or a partial range that is part of a larger merged cell.

VBA cannot clear or modify partial components of a merged cell. You must either unmerge the target cells in your worksheet or rewrite the VBA code to reference the entire merged range.

Locate the failing line of code

Open the Visual Basic Editor (ALT + F11), run your macro, and click "Debug" on the error dialog to highlight the specific line of code that is causing the error.

Check the worksheet for merged cells

Match the range reference in the highlighted code with your Excel worksheet. Select the target range and check if the "Merge & Center" button on the Home tab is highlighted.

Adjust the VBA code or unmerge the cells

If the cells do not need to be merged, click "Merge & Center" to unmerge them. If they must remain merged, modify your VBA code to reference the entire merged block (for example, use Range("A1:C1").ClearContents instead of Range("A1").ClearContents).

Clear Entire Range: Referencing the full dimensions of the merged range in your VBA code will successfully bypass the 1004 error and clear the contents as intended.

Solution 2

Fix Unassigned Variables and Update Range References

Rearranging columns can invalidate static range references in your code, and unassigned variables can lead to execution failures.

Manage and Run VBA Macros Seamlessly with WPS Office

WPS Spreadsheet offers comprehensive support for VBA macros, allowing you to run, edit, and debug automated tasks like invoice generation without compatibility issues.

1. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the invoice macro.
2. Access Developer Tools: Navigate to the "Developer" tab on the top ribbon to access the VBA editor and macro security settings.
3. Edit and debug the code: Click on "Visual Basic" to open the editor. Locate your macro, adjust any merged cell range references, and assign the missing variables.
4. Run the macro safely: Click the "Run" button or use your assigned form control button in the worksheet to execute the updated macro completely error-free.

Fully compatible with Microsoft Excel macro-enabled formats (.xlsm, .xlsb) and VBA scripts.Built-in advanced developer tools for identifying and debugging Run-time errors.Free and lightweight alternative for automating your complex spreadsheet tasks.Familiar user interface makes transitioning from other spreadsheet software seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why do merged cells cause Run-time error 1004 in VBA?

Merged cells combine multiple individual cells into a single addressable space. If a VBA script tries to modify, clear, or paste into only a portion of that merged range, Excel returns Error 1004 because it cannot manipulate a partial merged cell.

How can I find which variable is unassigned in my VBA macro?

You can step through your code using the F8 key in the Visual Basic Editor and hover over variables to see their current values. Adding "Option Explicit" at the very top of your code module is highly recommended, as it forces you to declare all variables and will instantly flag unassigned or misspelled variables before execution.

Does rearranging columns automatically update my VBA code?

No, rearranging columns or rows in a worksheet does not automatically update hardcoded cell references (such as Range("B2")) in your VBA code. You must manually update the code or use Named Ranges, which dynamically track their position even if they are moved on the worksheet.

Excel & Data Problems