To filter dates with VBA, apply AutoFilter to a range that includes its header row, then set date criteria on the date column. For reliable date ranges—including cells with times—use Excel date serial numbers and an inclusive start with an exclusive end, such as “on or after March 1 and before April 1.”
How VBA date filters work
Excel stores genuine dates as serial numbers: the whole-number portion represents a day, and any fractional portion represents a time on that day. AutoFilter compares those stored values. A cell displayed as 3/15/2025 might therefore contain a time as well as a date, even if the cell format hides the time.
In VBA, the AutoFilter method applies criteria to a range. Its Field argument identifies a column by its position inside that range—not by the worksheet’s column letter. If the filtered range starts in column A and the date field is column C, use Field:=3. The range must include the header row for this field number to correspond to the data columns as expected.
Date criteria are passed as text, but Excel can compare them to the underlying date serials. Building criteria from DateSerial values avoids relying on strings such as “3/4/2025,” whose interpretation can vary between regional date settings. This approach assumes the cells contain actual Excel dates, not text that merely looks like a date.
Filter a date range with VBA
This example filters a worksheet range to show dates in March 2025. It assumes headers are in row 1, the data is in columns A through D, and dates are in the third column of that range. Adjust the sheet name, range, field number, and dates for your workbook.
Option Explicit
Sub FilterMarch2025()
Dim ws As Worksheet
Dim dataRange As Range
Dim startDate As Date
Dim endDateExclusive As Date
Set ws = ThisWorkbook.Worksheets("Data")
Set dataRange = ws.Range("A1:D100")
startDate = DateSerial(2025, 3, 1)
endDateExclusive = DateSerial(2025, 4, 1)
dataRange.AutoFilter Field:=3, _
Criteria1:=">=" & CLng(startDate), _
Operator:=xlAnd, _
Criteria2:="<" & CLng(endDateExclusive)
End Sub
The filter includes March 1 and excludes April 1, so it returns all dates in March, including entries with times such as March 31 at 6:30 p.m. The cutoff is the first instant of April; using “less than” that cutoff avoids accidentally omitting times on the final day. DateSerial(year, month, day) also handles month and year boundaries correctly, so the same pattern works for December by using January 1 of the following year as the exclusive end.
Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11 and run the procedure from there or from the Macro dialog. On Mac, the VBA editor shortcut is Option+F11 (on some keyboards, use Fn as well); ribbon and shortcut availability can vary by keyboard settings. Excel for the web can open a workbook containing VBA but cannot create, run, or edit VBA macros.
Choose the right criteria for the job
The date range pattern is flexible: change the start and end boundaries to represent a day, month, year, or any custom period. Use a start-inclusive, end-exclusive pair when the goal is to include every time on each selected date.
- One calendar day: Set the start to the required date and the exclusive end to the following date. This captures every time on that day.
- One month: Use the first day of the month as the start and the first day of the next month as the end.
- One year: Use January 1 of the target year and January 1 of the following year.
- A business-defined period: Use the actual beginning and ending boundaries. If the intended end date should be included, set the exclusive end to the day after it.
For example, to show dates from May 10 through May 20, set the start to May 10 and the exclusive end to May 21. That is often clearer and safer than trying to construct an end criterion that means “less than or equal to the last possible time on May 20.”
Filter for one exact date
An exact-date criterion written as equality can be misleading if matching cells contain times. A cell holding March 15 at noon is not numerically equal to midnight on March 15, even if its visible display shows only the date. To match the entire calendar day, filter from the date’s midnight up to—but not including—the next midnight.
In the example macro, replace the start and end assignments with startDate = DateSerial(2025, 3, 15) and endDateExclusive = DateSerial(2025, 3, 16). Keep the same two AutoFilter criteria. This returns both midnight entries and entries later in the day. If your data is guaranteed to contain dates without times, an equality criterion may be sufficient, but the two-boundary approach is more robust when the source may change.
Use the correct range and field number
Many date-filter problems come from a range that does not match the data rather than from the date criteria. The AutoFilter range should include the header and all rows to be filtered. If new data is added below a fixed range such as A1:D100, it will not be included until the range is expanded. Avoid including unrelated columns or multiple header rows, as that can make the field number confusing.
For instance, if the range is B2:F500 and the date column is D, the field number is 3: B is field 1, C is field 2, and D is field 3. It is not 4 just because D is the fourth worksheet column. If the range starts at column D and the dates are in D, the field number is 1.
When the data is an Excel Table, using the table’s range can be more convenient than hard-coding the last row. A table expands as rows are added, and its header is already part of the table range. You can assign the table’s Range to a Range variable and apply AutoFilter to it, while still calculating Field relative to the table’s first column. Check that the date column is actually within the table.
Common date-filter problems
- Dates are stored as text: A value that looks like a date may be left-aligned or fail date arithmetic. Convert the source values to real Excel dates before filtering. Changing the number format alone does not convert text into a date.
- Regional settings change interpretation: Criteria assembled as strings like “04/05/2025” may be read as April 5 or May 4, depending on settings. Construct boundaries with
DateSerialand compare serial values instead. - Times are hidden: A displayed date may include an unseen time component. Use the start-inclusive, next-day-exclusive method when filtering by calendar day or a broader date period.
- The field number is wrong: Count from the first column of the AutoFilter range, not from worksheet column A. A wrong field can filter a different column or produce unexpected results.
- The range misses newer rows: Expand a fixed address or use a Table that grows with the data. Otherwise, recently added records remain outside the filter.
- Another filter is already active: Applying AutoFilter criteria to a field can replace the criteria on that field while filters on other fields may remain. If the result looks narrower than expected, inspect the other active filters too.
Also check for blank cells, inconsistent source data, and formulas that return text. A date column containing a mix of genuine dates, text dates, and blanks will not behave as one consistent set of date values. Cleaning the source column first is usually better than trying to compensate with increasingly complicated criteria.
Date ranges versus Excel’s date-group filters
Excel’s filter menu can show date-specific choices such as years, months, and date ranges. These grouped choices are convenient for a person filtering interactively, especially when browsing a large list. VBA can also work with AutoFilter date-group criteria, but those criteria have a different structure from a simple pair of numeric boundaries and can be less straightforward to maintain.
For a macro whose purpose is “show this month” or “show dates from A through B,” serial-number boundaries are usually easier to read, test, and adapt. They also make the treatment of times explicit. Use date-group criteria when reproducing a particular grouped selection from Excel’s filter UI is important; use ordinary comparisons when you need predictable date boundaries.
Clear or change the filter
Calling AutoFilter again with new criteria is a practical way to change the date window. To remove filtering and show all records, Excel’s worksheet object provides ShowAllData, but it should only be called when the sheet is in a filtered state. A common safeguard is to check ws.FilterMode before calling it. Clearing all filters may also reveal rows hidden by filters on other columns, so decide whether that is intended.
There is a difference between removing filter criteria and removing the filter arrows. Showing all data clears the current filtered result; toggling AutoFilter can also remove the dropdown controls. If users rely on those controls, clear the criteria without turning off the filter interface. A macro should avoid changing unrelated filters unless its purpose is to reset the whole list.
Frequently Asked Questions
How do I filter a date range in VBA AutoFilter?
Use two criteria on the date field: one greater than or equal to the start date and one less than the exclusive end date, joined with Operator:=xlAnd. Build the boundaries with DateSerial and convert them to serial numbers.
How do I filter one date and include times?
Use a lower boundary at midnight on the target date and an upper boundary at midnight on the following date, excluding that upper boundary. This includes every time during the target calendar day.
Does AutoFilter Field use the Excel column letter?
No. Field is the position of the column within the filtered range. If your range begins in column B and the dates are in column D, use field 3.
Why does my VBA date filter return no rows?
Check that the date cells are real Excel dates, the field number is relative to the selected range, and the range includes the data rows. Also look for hidden times, text dates, and active filters on other columns.
Can I run a VBA AutoFilter macro in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run the macro; the web version can still open and edit many workbook contents.
Related
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
- AI and Power Query: What It Can and Cannot Do in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Resize: Change a Range’s Size with VBA CodeIn Excel VBA, use the Resize property to refer to a range with a different number of rows…
- Excel VBA Range Using Cells: Syntax, Examples, and TipsIn Excel VBA, use Cells(row, column) to identify a cell by its row and column numbers, and pass…
- VBA Range Select: Examples, Uses, and Safer AlternativesIn Excel VBA, use Range.Select to select cells, but the worksheet containing the range must be active first.…
- VBA Close Workbook Without Saving: Methods and RisksTo close an Excel workbook with VBA and discard its unsaved changes, call the workbook’s Close method with…
- VBA Pivot Table Fields: How to Manage ThemIn Excel VBA, a PivotTable’s fields are accessed through its PivotFields collection: set a field’s Orientation to place…
- Excel VBA AutoFilter and Sort: Apply Both to Data SafelyYes. In Excel VBA, apply AutoFilter to a range that includes its headers, then sort that same complete…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.