Excel 365 VBA date not working in custom autofilter

Question

I have created the following code to filter a table (GrafanaPMT) to filter on all dates after today's date the future. The first few lines clears the current filters and the final line inserts the date into the excel table, but no rows are displayed (many row have dates into the future).

I've tried hard coding today's date in as well, and this also doesn't display any rows despite being added to the custom autofilter.

If i physically type in "19/08/2021" into the filter, over 1000 lines are displayed.

Any help would be much appreciated.

\[Date showing in autofilter]1

Sub MS4_not_6_over_3m()
'
' MS4_not_6_over_3m Macro
'
    Dim today As Date
    
    Sheets("Data").Select
    ActiveSheet.ListObjects("GrafanaPMT").Range.AutoFilter Field:=5, Criteria1:="SEAL-RAN"
    Rows("10:10").Select
    ActiveWorkbook.Worksheets("Data").ListObjects("GrafanaPMT").Sort.SortFields.Clear
    ActiveSheet.ShowAllData

    ActiveSheet.ListObjects("GrafanaPMT").Range.AutoFilter Field:=11, Criteria1:=">=" & Format(today, "dd/mm/yyyy")

End Sub

Try wrapping Format(today, "dd/mm/yyyy") in CLng or CDate or even DateValue. — norie

Elio Fernandes Elio Fernandes · Accepted Answer · 2021-08-19T14:42:12

Try this

Sub MS4_not_6_over_3m()
    Dim ws As Worksheet: Set ws = Sheets("Data")
    Dim ol As ListObject: Set ol = ws.ListObjects("GrafanaPMT")
    Dim olCol As Integer
    
    ' clear table filters
    If ol.AutoFilter.FilterMode Then ol.AutoFilter.ShowAllData
    
    ' Filter column 'mydate'
    olCol = ol.ListColumns("myDate").Index  ' Put here the name of your column
    ol.Range.AutoFilter Field:=olCol, Criteria1:=">=" & Format(Date, "mm/dd/yyyy")
    
    ' OR
    ' ol.Range.AutoFilter Field:=olCol, Criteria1:=">=" & CDbl(Date)
End Sub

Excel 365 VBA date not working in custom autofilter

2 Answers