CODEWITHSUNDEEP
vbaexcel
mccreaVBA
mccreaVBA

Reputation: 37

VBA Excel autofilter for any date before the current month

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.

Upvotes: 1

Views: 3629

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

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

Upvotes: 4

Related Questions