Fix Excel VBA EntireRow.Delete Error 1004 After Filtering
Question details
The user is experiencing Run-time error 1004 when attempting to delete filtered rows in an Excel table using the VBA EntireRow.Delete method.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to filter a table and delete the matching rows.
- Observed behavior
- The script fails with Run-time error 1004 at the EntireRow.Delete line, which is often caused by hidden columns or hidden rows intersecting the deleted range.
Open your VBA editor to review your code, and ensure you have saved a backup copy of your workbook since macro deletions cannot be undone using the standard Undo feature.
Use SpecialCells to Target Visible Rows Only
Modify your VBA code to explicitly select only the visible cells after filtering, preventing errors caused by hidden data.
When you filter a table, the rows that do not match the criteria are hidden. If you attempt to delete the entire DataBodyRange, VBA may throw Run-time error 1004 because it tries to interact with hidden cells.
Using the SpecialCells(xlCellTypeVisible) method ensures that only the rows currently displayed by the filter are selected and deleted.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor in Excel.
In the Project Explorer pane on the left, double-click the module that contains your filtering and deletion macro.
Modify your deletion line to include .SpecialCells(xlCellTypeVisible). For example, change it to: .ListObjects("Table1").DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete.
Save your code and click the 'Run' button (or press F5) to verify that the filtered rows are now deleted without throwing an error.
Unhide All Columns and Rows Before Execution
Ensure no columns or rows were hidden by previous macros, as intersecting hidden columns can block the EntireRow.Delete method.
Run and Edit VBA Macros Seamlessly in WPS Office
WPS Spreadsheets provides robust support for VBA macros, allowing you to automate tasks, filter tables, and manage data just like in Microsoft Excel.
- 1. Install WPS Office: Download and install WPS Office on your computer.
- 2. Open your macro workbook: Open your macro-enabled workbook (.xlsm) in WPS Spreadsheets.
- 3. Access the Developer tools: Go to the 'Developer' tab on the top ribbon and click 'VBA Editor' to view your scripts.
- 4. Edit and run your code: Paste or edit your VBA code to include the SpecialCells method, then click 'Run' to execute the macro smoothly.
Frequently Asked Questions
Why does EntireRow.Delete throw Run-time error 1004?
This error typically occurs when the range you are trying to delete intersects with hidden columns or rows, when the worksheet is protected, or when you attempt to delete a non-contiguous range without specifying visible cells.
What does xlCellTypeVisible do in VBA?
The xlCellTypeVisible constant is used with the SpecialCells method to target only the cells that are currently visible on the screen, intentionally ignoring any rows or columns hidden by filters or manual formatting.
Can I undo a macro deletion in Excel?
No, actions performed by a VBA macro, such as deleting rows, clear the application's undo history. You should always save a backup of your workbook before running destructive macros.
How do I unhide all columns using VBA?
You can quickly unhide all columns in the active worksheet by adding the line `ActiveSheet.Columns.EntireColumn.Hidden = False` to your VBA script before any data manipulation occurs.
