Solved autofilter using now in an array

October 3, 2016 at 05:31:32
Specs: Windows 7
Hello forum members, need help tweaking this autofilter code, I want to show anything in field 32 that has any day in the previous month. I was using "-30" but that gives me anything 30 days prior and what I really want is anything in the previous month.

    ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=32, _
           Operator:=xlFilterValues, Criteria2:=Array(1, Now - 30)

any thoughts?


See More: autofilter using now in an array

Reply ↓  Report •


✔ Best Answer
October 3, 2016 at 17:35:05
Ok I got it working using a helper column and a cell where I have the month() formula, probably not the most efficient way to do it but it works so it just comes down to simple math and the date will always be correct thanks to the month formula:

CloseoutMonth = Worksheets("Goals").Range("T1") - 1
ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=150, Criteria1:=CloseoutMonth

message edited by mecerrato



#1
October 3, 2016 at 07:44:19
I have not tested this as a Filter Criteria, but it currently returns 9 in a MsgBox:

Month(Now) - 1

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
October 3, 2016 at 08:56:48
I am getting a technical error I think based on my syntax (run-time error 1004 autofilter method of range class failed), point of clarification is that field 32 is a date format (9/27/2016):

ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=32, _
           Operator:=xlFilterValues, Criteria2:=Array(Month(Now) - 1)

message edited by mecerrato


Reply ↓  Report •

#3
October 3, 2016 at 09:13:51
Don't you still need the "1"?

I can't test anything at this time and I don't know AutoFilter code syntax off the top of my head. Maybe...

Array(1, Month(Now) - 1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
October 3, 2016 at 15:07:45
that didn't work; I tried a few variations but can't get it to work.

Reply ↓  Report •

#5
October 3, 2016 at 17:35:05
✔ Best Answer
Ok I got it working using a helper column and a cell where I have the month() formula, probably not the most efficient way to do it but it works so it just comes down to simple math and the date will always be correct thanks to the month formula:

CloseoutMonth = Worksheets("Goals").Range("T1") - 1
ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=150, Criteria1:=CloseoutMonth

message edited by mecerrato


Reply ↓  Report •


Ask Question