Column Formatting by Date in VBA

[fben96]

Hi,

I am trying to autoFilter a column by date. I have a whole bunch of dates inthis column. Out of date would be to filter the dates that are older than fiveyears old. and Expires within 6 months would be filter the dates between 4.5years and 5 years. Both filters from todays date. This is the code I amcurrently running but it hides all.

Sub RoundedRectangle12_Click()
Range("a5").Select
Selection.AutoFilter Field:=7, Criteria1:=">=Date-365*5+1"
End Sub

 

[mse330]

Hi

Try the below revised line & see if it does the job


Selection.AutoFilter Field:=7, Criteria1:=">=" & clng(Date-365*5+1)

 

[fben96]

YES! Do you have any idea what I would put to filter for the 4.5-5 year range?

 

[fben96]

I tied

Sub RoundedRectangle15_Click()
Range("a5").Select
Selection.AutoFilter Field:=7, Criteria1:=">=" & CLng(Date - 365 * 5 + 1), Criteria2:="<=" & CLng(Date - 365 * 4.5 + 1)
End Sub

 

[mse330]

I think this might be a little bit more accurate for the dates

Sub RoundedRectangle15_Click()
Rows(5).AutoFilter Field:=7, Criteria1:=">=" & CLng(DateAdd("m", -60, Date)), Criteria2:="<=" & CLng(DateAdd("m", -54, Date))
End Sub