CODEWITHSUNDEEP
excelvba
hunter
hunter

Reputation: 13

How to filter out all the dates that are older than 3 months , referencing the current date

i want to filter the dates that are older than 3 months and i want to have the system variable 

Dim Date3M_ago As Date
Date3M_ago = Worksheets("Engine").Range("C1")
Selection.AutoFilter
ActiveSheet.Range("A:C").AutoFilter Field:=46, Operator:= _
xlFilterValues, Criteria1:= Date3M_ago

'Worksheets("Engine").Range("C1") is the cell where i have the formula that shows me the date 3 months ago , referencing the current date

The problem is i can't write criteria1:= < Date3M_ago , because it gives me error. the correct syntax would be criteria1:= "< 8/28/2019" but that's a fixed date and i don't need that.

Upvotes: 1

Views: 2470

Answers (3)

Plutian
Plutian

Reputation: 2309

Autofilter cannot be applied to a column outside of the range you are trying to autofilter. In your example you are trying to filter column AT (Field:=46) but only apply the range from column A to C. Either apply column AT to your autofilter like so:

ActiveSheet.Range("A:AT").AutoFilter Field:=46, Operator:= _

Or change your field to be one included in the range:

ActiveSheet.Range("A:C").AutoFilter Field:=3, Operator:= _

Also a suggestion, replace the reference to cell filled with your date 30 days ago with a vba calculation instead to prevent breaking:

Date3M_ago = DateAdd("m", -3, Now)

Upvotes: 1

JvdV
JvdV

Reputation: 75840

Hopefully something in here is usefull for you:

You say you need to filter out dates older than today minus three months (as that is what your formula in Worksheets("Engine").Range("C1") does. The same can be done through VBA with Application.WorksheetFunction.EDate(Date, -3),or even DateAdd as per @Plutian his answer. If this is not what your intention was, you can instead reference your own variable again in it's place but make sure to turn it into a double value to use within the filter > CDbl(Date3M_ago).

  • Why do we turn this into a double? Depending on your Windows Regional Settings, the use of dates as criteria can give you unexpected results. This is because Excel sees the dates as a US date. One way around this, as per the link, is to use Long variables, however using Double variables will work with decimals and therefor it's saver to use CDbl in my option to account for date-time values.

If we put the above into practice with some sample data like below (dates are formatted "d-m-yyyy":

enter image description here

A piece of code to filter on column C would look like:

Sub Test()

Dim Date3M_ago As Date
Date3M_ago = Worksheets("Engine").Range("F1")
With ThisWorkbook.Sheets("Sheet1") 'Change accordingly
    'All options below will work, remove the lines you don't want to use:
    .Range("A:C").AutoFilter Field:=3, Criteria1:=">" & Application.WorksheetFunction.EDate(Date, -3)
    .Range("A:C").AutoFilter Field:=3, Criteria1:=">" & CDbl(DateAdd("m", -3, Date))
    .Range("A:C").AutoFilter Field:=3, Criteria1:=">" & CDbl(Worksheets("Engine").Range("F1"))
    .Range("A:C").AutoFilter Field:=3, Criteria1:=">" & CDbl(Date3M_ago)
End With

End Sub

Notice how the field to be filtered is in the range to be filtered, e.g.: the third column? The output would look like:

enter image description here

Upvotes: 1

Mikku
Mikku

Reputation: 6654

You can try this:

Dim Date3M_ago As Date

Date3M_ago = Worksheets("Engine").Range("C1")

ActiveSheet.UsedRange.AutoFilter Field:=46, Criteria1:="<" & Date3M_ago, Operator:=xlAnd

Upvotes: 1

Related Questions