VBA Filter by Dates

[Simondo]

Hi All,

I have a spreadsheet with a list of employee names, in the adjacent columns are a start date, and end date.

I want to filter the start date by "before or equal to" today
and the end date by "after or equal to" today

I have been trying non stop, so far I have the following code, which will only return the dates if they equal today's date.

Sub Dates()

Dim DateToday As Date
DateToday = Worksheets("Date").Range("A1").Value


    Worksheets("Histogram").ListObjects("Table_owssvr_1").Range.AutoFilter Field:=5, _
        Criteria1:=Array(2, DateToday), Operator:=xlAnd
    Worksheets("Histogram").ListObjects("Table_owssvr_1").Range.AutoFilter Field:=6, _
        Criteria1:=Array(2, DateToday), Operator:=xlAnd
        
End Sub

I'm not sure how to set it to filter for any dates before or after.

Any help greatly appreciated

Regards,

 

[ParamRay]

Try it like this instead:

Sub FilterDates()
  
  Dim Today As Date
  Today = Worksheets("Date").Range("A1").Value
  
  With Worksheets("Histogram").ListObjects("Table_owssvr_1").Range
    .AutoFilter Field:=5, Criteria1:="<=" & CLng(Today)
    .AutoFilter Field:=6, Criteria1:=">=" & CLng(Today)
  End With

End Sub

 

[Simondo]

Hi gpeacock,

that worked perfectly, was stuck on that one for a while

Thanks