EXCEL FEED: Re: [ExcelVBA] Auto Filter Help

Selasa, 09 Juni 2015

Re: [ExcelVBA] Auto Filter Help

hmmm... Okay... I was after the code for specifying different columns really as that seemed to me to be needed by the OP.

>.... and if so, what are the filters that are applied so it would be possible to put the filters back.

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

Variant?

Actually now I think about it Paul... do you think using a "table" would help if the correct version of Excel is being used?

Lisa

Sent: Tue, Jun 9, 2015 3:42 pm
Subject: Re: [ExcelVBA] Auto Filter Help

           Lisa,          
           
  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).          
           
            Paul           
  -----------------------------------------            
            "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 1z@compuserve.com [ExcelVBA]" <ExcelVBA@yahoogroups.com>
 To: ExcelVBA@yahoogroups.com 
 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?                      
                      
  Lisa                     
                      
  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: Green <1z@compuserve.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (5)

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Visit Your Group