Clear cell if the date in the cell is less than a cutoff date

[PistolPete123]

Hello everyone,

This is my first post and I have been stuck for a bit trying to find a solution. I am trying to clear any cell in column F if the date inside the cell is less than the current date minus 7 days. Also the cells in column F have formulas which calculate a date.

What I tried so far hasn't worked:

Dim currentDate As Date
currentDate = DateAdd("d", -7, Date)
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "F").End(xlUp).Row
With ActiveSheet.Range("F2:F" & Lastrow)
.AutoFilter Field:=1, Criteria1:="<" & currentDate, Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Range.Delete
End With
ActiveSheet.AutoFilterMode = False

 

[AlphaFrog]

You could have your formula only display valid dates...

=IF(YourDateFormulaHere < Today()-7, "", YourDateFormulaHere)