VBA & Macro Problems

How to Fix Excel VBA AutoFilter Error 1004 When Deleting Rows

Khadija Khan Sep 30, 2026 868 views

Question details

The user needs to resolve a Run-time error 1004 that occurs when executing a VBA macro designed to filter and delete specific rows.

How to Fix Excel VBA AutoFilter Error 1004 When Deleting Rows

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Running a VBA macro to apply an AutoFilter across a large worksheet to find and delete rows containing specific text like 'broker', 'car', or 'motor'.
Observed behavior: The macro halts execution and returns an Error 1004 when applying the AutoFilter method to the defined range.

Before you start

Before modifying and running your VBA code, create a sanitized, reduced copy of your workbook to test the macro without risking accidental deletion of your original data.

Solution 1Recommended

Include a Header Row in the AutoFilter Range

The AutoFilter method in VBA requires a header row to function properly. Specifying a data-only range without headers is a common cause for Run-time error 1004.

When Excel or WPS Spreadsheet applies an AutoFilter via VBA, it assumes the first row of the specified range is the header. If you only select the data body, the filter criteria may clash with the layout, triggering the 1004 error.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor, and navigate to the module containing your macro.

Locate the AutoFilter Statement

Find the line of code that applies the filter, which usually looks like ws.Range("...").AutoFilter.

Include the Header Row in the Range

Modify the range coordinates so that it starts at your header row. For example, if headers are in row 1, update the code to: ws.Range("A1:A284").AutoFilter Field:=1, Criteria1:="Broker"

Test the Macro

Save your code and run the macro on your reduced test workbook to verify that the rows filter and delete correctly without errors.

Include a Header Row in the AutoFilter Range

Debug Mode: You can step through your macro line-by-line by pressing F8 in the VBA editor. This helps you visually confirm that the AutoFilter is applied to the correct headers before the deletion command triggers.

Seamless VBA Macro Support

Use WPS Spreadsheet for Seamless VBA Macro Execution

WPS Office provides an excellent environment for handling VBA macros. You can edit, debug, and run your VBA code directly in WPS Spreadsheet with a familiar interface, ensuring your automated data tasks run smoothly.

1. Open Your Workbook: Launch WPS Spreadsheet and open your .xlsm or .xls file containing the macro.
2. Access the Developer Tab: Click on the 'Developer' tab in the top ribbon menu to access advanced macro tools.
3. Open the VBA Editor: Click on the 'Visual Basic' icon to open the editor and update your AutoFilter range to include headers.
4. Run the Macro: Click 'Run' or press F5 to execute your fixed macro and automate your row deletion efficiently.

Fully compatible with Microsoft Excel (.xls, .xlsx, .xlsm) macro-enabled formatsBuilt-in Developer tab and VBA editor for writing and debugging codeFast and lightweight performance when filtering and deleting rows in large datasetsFree to download and use for everyday office productivity

microsoft office alternative - wps office

Frequently Asked Questions

Why does VBA AutoFilter return Run-time error 1004?

Error 1004 during AutoFilter typically occurs when the specified range does not include a header row, the field index is out of bounds, the worksheet is protected, or there are merged cells within the filter range.

How can I safely delete visible rows after applying a VBA AutoFilter?

After applying the filter, you can use 'Range.SpecialCells(xlCellTypeVisible).EntireRow.Delete' to remove the filtered rows. Always ensure you offset the selection from the header row so your headers do not get deleted.

Can I filter multiple criteria using VBA AutoFilter?

Yes, you can filter multiple criteria by adding 'Operator:=xlOr' or 'Operator:=xlAnd' and specifying 'Criteria2' in your AutoFilter statement. For more than two items, you can pass an array to the Criteria1 parameter.

Does WPS Office support Excel VBA macros?

Yes, WPS Office Pro and select business versions fully support VBA macros. You can run, edit, and save .xlsm files with high compatibility using the built-in Developer tools.

Excel & Data Problems