How to Filter Dates in the Last N Days Using Excel VBA
Question details
The user wants to create an Excel VBA macro to filter rows where dates in a specific column fall within the previous N days (such as 50, 60, 90, or 120 days) from the current date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Writing an automated script to filter datasets dynamically based on a rolling date window relative to today.
- Observed behavior
- Using CurrentRegion or converting the date parameters to Long values within the macro causes the AutoFilter to return incorrect data or an empty result set.
Verify that your target column contains valid Excel serial dates rather than text strings, and ensure your dataset has a distinct header row before running the macro.
Use EntireColumn with Direct Date Criteria
Applying the AutoFilter to the entire column and using dynamic date criteria avoids the data truncation pitfalls of CurrentRegion and the formatting errors of Long conversions.
Instead of converting date variables to Long values or relying on CurrentRegion (which might miscalculate range boundaries if there are blank rows), applying AutoFilter directly to the entire target column guarantees accurate date filtering. By using the 'Date' function in VBA, you can easily set a criteria window.
Press Alt + F11 in your Excel workbook to launch the Visual Basic for Applications (VBA) editor.
Right-click on your workbook name in the Project Explorer, select 'Insert', and click 'Module'.
Paste a subroutine that accepts an integer N. Target your specific column (e.g., Range("$F$1").EntireColumn) and apply the AutoFilter method. Set Criteria1 to ">=" & Date - N, Operator to xlAnd, and Criteria2 to "<=" & Date.
Create a calling macro (such as Sub FilterLast50) that executes your main subroutine and passes the desired number of days (e.g., 50, 60, or 90) as the argument.
Incorporate an Input Box for User Prompting
Enhance your macro by prompting the user to manually enter the number of days they wish to filter, making the code dynamic for any timeframe.
Filter Dates and Run VBA Macros Natively in WPS Office
WPS Office provides robust support for VBA macros and advanced filtering functions, allowing you to run your Excel VBA scripts seamlessly while managing large datasets with ease.
- 1. Install WPS Office: Download and install WPS Office, then open your macro-enabled workbook (.xlsm).
- 2. Access the Developer Tab: Navigate to the Developer tab on the ribbon to access the built-in VBA Editor.
- 3. Run Your Macro: Paste your date-filtering macro and execute it exactly as you would in Microsoft Excel.
- 4. View Filtered Results: Return to your spreadsheet to view the accurately filtered dates applied directly to your column.
Frequently Asked Questions
Why does my VBA filter return an empty dataset when filtering dates?
This usually happens if the dates in your column are formatted as text instead of valid Excel serial dates, or if you converted the dates to Long values in VBA before applying the filter. Ensure your dates are valid and use standard VBA Date functions without type conversion.
Can I apply the AutoFilter starting from row 2 if I have headers?
Yes. When applying the .AutoFilter method to an EntireColumn (like column F), Excel generally identifies the first row as the header. If you encounter issues, you can specify the exact range starting from your header, for example: Worksheets("Sheet1").Range("F1:F1000").AutoFilter.
What happens if there are no dates in the past 50 days?
If no dates in your dataset match the criteria (falling between today and 50 days ago), the AutoFilter will hide all data rows, leaving only the header row visible on your worksheet. It will not cause a runtime error.
