Selasa, 09 Juni 2015

Re: [ExcelVBA] Auto Filter Help



In this case, recording a macro doesn't help much.

With AutoFilter, each column gets set as separate steps.
Let's say you want to select rows in which:
column D is "H" or "I"
column N is 6/5/2015
and column AC is "N",

the code to CREATE that is:
    ActiveSheet.Range("$A$1:$BV$100000").AutoFilter _
        Field:=4, Criteria1:="=H", Operator:=xlOr, Criteria2:="=I"
    ActiveSheet.Range("$A$1:$BV$100000").AutoFilter _
        Field:=14, Operator:=xlFilterValues, Criteria2:=Array(0, "6/5/2015")
    ActiveSheet.Range("$A$1:$BV$100000").AutoFilter _
        Field:=29, Criteria1:="N"
But for each line of code, the Autofilter.Filters collection is updated.

Basically, you have to enumerate the .Filters collection and store the values so that they can be restored later.

So, for column "D" (#4), the collection:
Activesheet.AutoFilter.Filters(4) has:
Criteria1 = "H", Criteria2 = "I" and Operator = xlOr

But the problem comes with the collection values for .Filters(14) 
Since Criteria2 is an array, it doesn't show up in the Criteria1/2 value.

I haven't figured out how to examine (and store) the array value to restore it later.

So, it's not quite as easy as it sounds.

I'm looking into it (in my spare time)...
but it's not obvious (to me).

"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley

From: "Green [ExcelVBA]" <>
Sent: Tuesday, June 9, 2015 8:19 AM
Subject: Re: [ExcelVBA] Auto Filter Help

Have you tried recording setting autofilter and then setting a specific filter to see how excel does it?


Sent: Mon, Jun 8, 2015 7:34 pm
Subject: [ExcelVBA] Auto Filter Help
I have a macro that will, on occasion, insert a column into a very large spreadsheet.  I found that if I have and auto filter set, that the process of inserting the column can take many minutes to perform (large spreadsheet and slow computer).  So I wrote an If() statement in the code to check for Auto Filter being on, and if so, turn it off.  Then continue to insert the column.  What I need help with is I'd like the code to see if the filter is on, and if so, what are the filters that are applied (maybe multiple column filters on), save them, turn off the autofilter, insert the column, then re-apply the auto filter.  Is this possible?


Posted by: Paul Schreiner <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: to enter the ezine, then search the ARCHIVES for EXCEL VBA.

Visit our ExcelVBA group home page for more info and support files:

More free tutorials and resources available at:




Tidak ada komentar:

Posting Komentar