CODEWITHSUNDEEP
vbaexcel
Ben
Ben

Reputation: 21625

Excel autofilter dates in month period

The following code filters a range of dates by the dates in August

ActiveSheet.Range("$A$29:$CG$3582").AutoFilter field:=18, Criteria1:= _
    xlFilterAllDatesInPeriodAugust, Operator:=xlFilterDynamic

How do I make this code dynamic so that I can filter the range by any month (e.g. month(today()))?

Upvotes: 2

Views: 10989

Answers (2)

Ben
Ben

Reputation: 21625

Ended up doing...

Function GetDatesInPeriodConst(month As Long)
    GetDatesInPeriodConst = 20 + month
    '(January is number 21)
End Function

and

ActiveSheet.Range("$A$29:$CG$3582").AutoFilter field:=18, Criteria1:= _
GetDatesInPeriodConst(month(Date)), Operator:=xlFilterDynamic

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6856

Try Criteria2, this is a bit cryptic though. Pass Array(1, datestring), where 1 will filter the entire month of that date, and the datestring must be in m/d/y format.
(0 = entire year, 2 = day, and some more, as discussed somewhere here).

Dim s As String
s = Format(Now, "MM""/""dd""/""yyyy")

ActiveSheet.Range("$A$29:$CG$3582").AutoFilter _
    Field:=18, Operator:=xlFilterValues, _
    Criteria2:=Array(1, s)

Upvotes: 2

Related Questions