Advanced autofilter with dates

[mecerrato]

I have the following code that I customized after getting help from a forum member. I am trying to add 1 additional criteria using a date test but cannot get it to work. I want the to filter out any rows where column X of each row is only 2 days old. So if the date in column X is 2 days old I don't want it as part of the data shown.

I am pretty sure the issue is with this part of the code but not sure what the right syntax is to achieve the result:

.Cells(15, "CU").Value = "<" & Date - 2

can anyone help me get this working?
See full procedure below:

Sub Filter_by_Red_CD_LE()
ActiveSheet.Unprotect
Dim r As Range
Dim dataRange As Range
Dim criteriaRange As Range

With Worksheets("Pipeline")
' Define the data range.
Set dataRange = .Range("A10:CP500")
'Define criteria range
Set criteriaRange = .Range("CQ10:CU12")
' Copy column headers
.Cells(10, "CQ").Value = .Cells(10, "S").Value
.Cells(10, "CR").Value = .Cells(10, "CK").Value
.Cells(10, "CS").Value = .Cells(10, "AZ").Value
.Cells(10, "CT").Value = .Cells(10, "H").Value
.Cells(10, "CU").Value = .Cells(10, "X").Value

'Define Criteria values
.Cells(11, "CQ").Value = "Red"
.Cells(12, "CR").Value = "Red"
.Cells(13, "CS").Value = "<>" & "*DOC*"
.Cells(14, "CT").Value = "<>" & "*Broker*"
.Cells(15, "CU").Value = "<" & Date - 2
End With

' Apply the filter.
dataRange.AdvancedFilter xlFilterInPlace, criteriaRange
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollRow = 11
Range("A8").Value = "Current Filter = RED LE/CD"
Range("CQ10:CU12").ClearContents
End Sub

 

[dmt32]

Hi,
try this update to your criteria code & see if helps

.Cells(15, "CU").Value = "<" & Format(Date - 2, "yyyy/mm/dd")

Dave

 

[mecerrato]

Hi,
try this update to your criteria code & see if helps

.Cells(15, "CU").Value = "<" & Format(Date - 2, "yyyy/mm/dd")

Dave

Thanks for trying but that did not do anything

 

[dmt32]

Thanks for trying but that did not do anything

Have you adjusted the criteria range in your code to include the additional filter?

Set criteriaRange = .Range("CQ10:CU12")

Dave