Excel Error Codes

How to Fix Excel VBA Runtime Error 1004 When Adding a Table Row

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to resolve an issue where an Excel VBA macro fails and crashes while attempting to insert a new row into an existing table.

Fixing Excel VBA Runtime Error 1004 When Adding a Table Row

Product: Microsoft Excel
Device & OS: not provided
Scenario: Executing a VBA macro that uses the ListRows.Add method to append data to the end of a formatted Excel table.
Observed behavior: Excel throws Runtime Error 1004 and may crash during the row insertion process, even though the workbook is not protected.

Before you start

Create a secure backup copy of your original workbook before modifying VBA code or deleting modules to prevent accidental loss of important data or macros.

Solution 1Recommended

Isolate the Issue Using a Stripped-Down Workbook

Create a simplified version of your workbook to determine if conflicting macros, background events, or corrupted data connections are triggering the 1004 error.

Runtime Error 1004 is often caused by conflicts with other background processes or workbook corruption rather than the ListRows.Add code itself. By stripping away unrelated elements, you can easily identify the root cause.

Create a simplified copy

Save a copy of your workbook and delete all non-essential worksheets, user forms, and VBA modules, leaving only the target table and the required macro code.

Verify table references

Update your VBA code to use a fully qualified reference to the table. For example, use 'ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").ListRows.Add' instead of relying on the active sheet.

Disable application events temporarily

Open the VBA Editor (Alt + F11), open the Immediate Window (Ctrl + G), type 'Application.EnableEvents = False', and press Enter to ensure no Worksheet_Change events are interfering.

Test and restore components

Run the macro in this simplified environment. If it works, gradually copy your other modules and data back into the file one by one, testing after each addition to isolate the conflicting element.

Isolate the Issue Using a Stripped-Down Workbook

Protect Sensitive Data: If you plan to share your stripped-down workbook on community forums for further help, ensure all confidential or personal information is completely removed from the table.

Solution 2

Clear Table Filters and Verify Structural Space

Ensure the table is not actively filtered and has sufficient empty rows beneath it to allow for expansion.

Free Microsoft Office alternative

Experience Stable Macro Support with WPS Office

Struggling with unexpected crashes and VBA runtime errors? WPS Office offers a lightweight, highly compatible alternative to Microsoft Office. It seamlessly handles macro-enabled workbooks without the heavy system requirements or frequent crashing.

Free to use with a highly intuitive, user-friendly interfacePerfect format compatibility with Microsoft Excel (.xlsx, .xlsm, .csv)Reliable performance for executing macros and handling complex datasetsLightweight installation that runs smoothly on older devices

microsoft office alternative - wps office

Frequently Asked Questions

What does Runtime Error 1004 mean in Excel VBA?

Runtime Error 1004 is a generic 'application-defined or object-defined error'. It usually means VBA is trying to interact with a range, sheet, or object that does not exist, is misspelled, or is currently blocked by worksheet protection or layout constraints.

Why does ListRows.Add fail even when my worksheet is unprotected?

Even on an unprotected sheet, this method can fail if the table is currently filtered, if there is data directly beneath the table blocking its expansion, or if another macro (like an active Worksheet_Change event) crashes when the new row triggers it.

How do I properly reference an Excel table in VBA to avoid errors?

Always use fully qualified references so VBA knows exactly where the table is located, regardless of which sheet is currently active. A proper structure looks like: Worksheets("YourSheetName").ListObjects("YourTableName").

Microsoft Office Error Codes