Macro for filtering dates

[BarryL]

Hi All,

In column F i have a list of dates that change every day (approx. 40 but 20 could be T-1, 10 for T-2, 5 T-3 etc. etc.). Usually the majority are the same so the spread is quite small. What I am looking for is a macro that will add a filter in and leave one date in at a time in the list. As this list changes every day as things update i can't record it. Below is my current code which isnt 100% what i want.

Sub d_filter()
Dim dt As Date
dt = Date$
Z = dt - 1
c = "=" & Z
ActiveSheet.Range("F1").AutoFilter _
field:=6, _
Criteria1:=c

Selection.AutoFilter
Selection.AutoFilter

Z1 = dt - 2
c1 = "=" & Z1
ActiveSheet.Range("F1").AutoFilter _
field:=6, _
Criteria1:=c1
Selection.AutoFilter
Selection.AutoFilter

Z2 = dt - 3
c2 = "=" & Z2
ActiveSheet.Range("F1").AutoFilter _
field:=6, _
Criteria1:=c2

End Sub



any help would be great.

 

[Andrew Poulsom]

In what way isn't your VBA code working?

 

[BarryL]

if there are dates outside of those. i was hoping i wouldnt have to enter that all the way down to like -20 or something.

 

[Andrew Poulsom]

You want a loop?

Sub d_filter()
    Dim dt As Date
    Dim i As Long
    Dim c As String
    dt = Date$
    For i = 1 To 20
        With ActiveSheet
            .AutoFilterMode = False
            c = "=" & dt - i
            .Range("F1").AutoFilter _
                field:=6, _
                Criteria1:=c
        End With
    Next i
End Sub