Selasa, 09 Juni 2015

Re: [ExcelVBA] Auto Filter Help


> Is this possible?
Simple answer ... yes, I think so.  :-)
But it will involve a bit (lot?) of playing around to get it right.
There is info on the interweb about grabbing the criteria information - e.g.
The complete set of autofilters for a sheet is an attribute of the sheet itself.  Again, there's code examples out there for working with the set of autofilters.
Setting them back on at the end should be relatively straightforward.  (Again, there are examples around for setting up criteria.)
If my understanding is correct, there is no direct correlation between a filter and a column, so you'll certainly need a repeatable method of reestablishing the filters after the column insert (presumably by ensuring that all columns from A are autofiltered.  This would probably require your column-insert code to put something into the top cell of the inserted column, so that Excel will put filters onto all columns.
Hopefully (but I don't know this) the set of filters will run from column A sequentially across the spreadsheet, so you can compensate for the inserted column without any trouble.
Worth doing a detailed Google search, and having a play.  (Sorry, I've never needed to do this, so can't send you any code.)

Regards, Dave S
----- Original Message -----
Sent: Tuesday, June 09, 2015 3:21 AM
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?

No virus found in this message.
Checked by AVG -
Version: 2015.0.5961 / Virus Database: 4355/9974 - Release Date: 06/08/15


Posted by: "David Smart" <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
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