VBA Excel autofilter for any date before the current month

1
votes

I'm trying to filter a date column for any date that falls before the 1st of the current month. So something similar to below:

ActiveSheet.Range("$A:$BF").AutoFilter Field:=12, Criteria1:= _
    xlFilterYearToDate, Operator:=xlFilterDynamic

But doesnt include any dates in the current month it runs on.

Edit: So any date that falls before the first of the current month, within the current year. If the filter was applied now, it would only show dates with the month of January 2015. If applied In August, only resulting visible dates should be anything within January-July 2015.

1
vbaexcel
Please specify which dates should be visible. - Gary's Student
Thanks for taking a look. So any date that falls before the first of the current month, within the current year. If the filter was applied now, it would only show dates with the month of January 2015. If applied In August, only resulting visible dates should be anything within January-July 2015. - mccreaVBA

1 Answers

4
votes

This should work for you:

Dim dtStart As Date
Dim dtFinal As Date

dtStart = CDate(Evaluate("DATE(YEAR(NOW()),1,1)"))
dtFinal = CDate(Evaluate("EOMONTH(TODAY(),-1)"))

ActiveSheet.Range("A:BF").AutoFilter 12, ">=" & dtStart, xlAnd, "<=" & dtFinal