VBA & Macro Problems

How to Filter Dates by Yesterday or Month with Excel Macro and Fix Error 1004

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user needs a VBA macro that automatically filters a date column based on a dropdown selection (Yesterday, This Month, Last Month) and needs to resolve a Runtime Error 1004 associated with the AutoFilter method.

How to Filter Dates Dynamically Using an Excel Macro Dropdown

Product: Excel
Device & OS: not provided
Scenario: Setting up a dynamic date filter triggered by a dropdown menu selection using a VBA Worksheet_Change event.
Observed behavior: The current macro triggers a Runtime error 1004 (AutoFilter method of Range class failed) because the defined filter range or the Field index number is incorrectly configured.

Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your spreadsheet security settings before running VBA code.

Solution 1Recommended

Implement a Worksheet_Change Event for Dynamic Filtering

Use a VBA event to monitor the dropdown cell, clear any existing filters, and automatically apply the corresponding dynamic date filter when the user changes the selection.

To make the filter update automatically without clicking a button, you must place the VBA code in the specific sheet's code module rather than a standard module.

The code uses the xlFilterDynamic operator alongside predefined Excel constants like xlFilterYesterday, xlFilterThisMonth, and xlFilterLastMonth.

Create a Data Validation Dropdown

Select the cell where you want the dropdown (e.g., cell I8). Go to the Data tab, click Data Validation, choose 'List', and enter 'Yesterday, This Month, Last Month' as the source.

Open the VBA Editor

Right-click the sheet tab containing your data and select 'View Code' to open the VBA Editor directly in the correct Worksheet module.

Add the Worksheet_Change Code

Set up a Private Sub Worksheet_Change(ByVal Target As Range) event. Use an 'If Not Intersect' statement to check if the changed cell (Target) is your dropdown cell.

Clear Existing Filters

Inside the event, add 'If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData' to clear previous filters before applying a new one.

Apply the Dynamic AutoFilter

Use a Select Case statement based on the dropdown value. Apply the filter to your data range using Operator:=xlFilterDynamic and the corresponding Criteria1 (e.g., xlFilterThisMonth). Set Field:=5 if your date column is the 5th column in your defined data range.

Implement a Worksheet_Change Event for Dynamic Filtering

Dynamic Constants: Using xlFilterDynamic automatically handles leap years and varying month lengths without requiring you to calculate exact start and end dates in VBA.

Solution 2

Resolve Runtime Error 1004 in AutoFilter

Fix the 'AutoFilter method of Range class failed' error by correcting the Field index number so it matches the relative column position in your data range.

Efficient Spreadsheet Management

Filter Dates Dynamically Using WPS Spreadsheet

WPS Office provides robust and seamless support for VBA macros, allowing you to easily automate complex tasks like dynamic date filtering with dropdowns.

1. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
2. Access the VBA Editor: Go to the Developer tab and click on 'VBA Editor', or simply press Alt+F11.
3. Insert the Event Code: Double-click your target worksheet in the Project Explorer and paste your Worksheet_Change event code.
4. Test the Dropdown: Return to the spreadsheet and select 'Yesterday' or 'This Month' from your dropdown to instantly filter the data.

Fully compatible with Microsoft Excel VBA macros and .xlsm filesAdvanced filtering and data validation tools built-inLightweight, fast spreadsheet processing for large datasetsFree and intuitive user interface with a familiar layout

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get Runtime Error 1004 when running my Excel filter macro?

This error usually happens when your VBA AutoFilter method references a Field index that does not exist in the specified range. For example, if your filtered range is 3 columns wide, setting Field:=4 will trigger Runtime Error 1004.

What is the difference between xlFilterDynamic and standard date criteria?

Using standard date criteria requires you to format exact start and end dates as strings. The xlFilterDynamic operator utilizes built-in Excel logic (like xlFilterThisMonth) to automatically calculate the current month's start and end dates based on the system clock.

How do I clear an AutoFilter using VBA before applying a new one?

You can clear existing filters by using the command 'If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData'. This ensures that the new filter criteria are applied to the entire dataset rather than filtering already hidden rows.

Can I use multiple dynamic filters at the same time in VBA?

Yes, you can apply multiple filters to different columns by adding additional AutoFilter lines with different Field index numbers. However, a single column can generally only have one dynamic date filter applied at a time.

Excel & Data Problems