none
VBA Autofilter state Excel 2010

    Pregunta

  • I need to find out the state of a table column filter - especially dates.  The new more complex filters in xl2010 cannot be found in Criteria1 or 2 - they stop macros as soon as you look at them if the operator is a list of values or any of the grouped date filters.  A recorded macro shows them being set via an array passed to the criteria but they don't seem available for viewing there.

    Question - where did they put the multiple criteria values?

    sábado, 23 de octubre de 2010 7:28

Respuestas

  • The help is not very on this topic - I did some experiments and you might find this table useful.  Sorry I don't know how to use these forum pages very well so it may not come out well.  For Lou Sigety's problem I think it is that any reference to Criteria1 for Operator values of 7 and above will cause an error unless it is moved into an appropriate type (array of some sort with 7 and type Interior for 8 & 12).  The dynamic filter has a number in Criteria1 that indicates the position in the various menus for selecting the filter - can't find an enumeration name for these so risky to use as they may change at any time.  The main irritation is with dates.  The grouped date selections are a total mystery but worse for me is the necessity of reformatting the criteria string if it contains a date with anything more complex than a simple "=".

    Hope that helps a bit.

     

    Filter - User Actions Data Operator Name Operator Count Cr1 VarType Criteria Type Name Criteria1 Criteria2
    Text Values/Equals/single value Text MySingleSelection 0 1 8 vbString "=No"
    Text Values/ custom OR Text xlOr 2 2 8 vbString "=No" "=Yes"
    Pick 4 values from list Text xlFilterValues 7 4 8204 vbArray ...of variants
    Custom <>space Text MySingleSelection 0 1 8 vbString <> 
    Select fill color Text xlFilterCellColor 8 1 9 vbObject Interior
    Select no fill Text xlFilterNoFill 12 1 9 vbObject Interior
    Select font color automatic Text xlFilterfFontAutomatic 13 1 3 vbLong 0
    Pick 1 value from list Text MySingleSelection 0 1 8 vbString "=X"
    Custom = one value Num MySingleSelection 0 1 8 vbString "=365"
    Pick - Select all Num MySingleSelection vbEmpty
    Choose top 4 items Num xlTop10Items 3 1 8 vbString ">=12"
    Custom between  Num xlAnd 1 2 8 vbString ">=7" "<=25"
    Choose above average Num xlFilterDynamic 11 1 3 vbLong 33
    Choose below average Num xlFilterDynamic 11 1 3 vbLong 34
    Date Values/Equals/single date Date MySingleSelection 0 1 8 vbString "=25-May-44"
    Date Values/Equals/date range Date xlAnd 1 2 8 vbString ">=40360" "<=40474"
    Date Values/After 30 Jun 10 Date MySingleSelection 0 1 8 vbString ">40359"
    All dates in period (month) Date xlFilterDynamic 11 1 3 vbLong 30
    All dates in last week Date xlFilterDynamic 11 1 3 vbLong 5
    Custom filter d1 or d2 Date xlOr 2 2 8 vbString "=21-Aug-43" "=25-May-44"
    Custom filter is after d in list Date MySingleSelection 0 1 8 vbString ">26216"
    Pick 2 dates from list Date xlOr 2 2 8 vbString "=21-Aug-43" "=25-May-44"
    Pick 3 dates from list Date xlFilterValues 7 3 8204 vbArray … of variants
    Group dates - pick 1 group Date xlFilterValues 7 0 Undef #N/A
    Group dates - pick 1 date Date xlFilterValues 7 0 Undef #N/A
    Group dates but use date value Date MySingleSelection 0 1 8 vbString ">40478"
    sábado, 30 de octubre de 2010 1:33

Todas las respuestas

  • You CAN get the values back, but its complicated.  Say we start with a simple table in A1 thru C20:

     

     

    tag value key
    4009 Alice 0.312874
    2579 Boris 0.20145
    3987 James 0.471108
    4205 Alice 0.799483
    4484 Alice 0.824626
    1340 James 0.687692
    2464 Mike 0.026245
    6256 James 0.17875
    3128 David 0.770483
    9699 David 0.841251
    2374 John 0.885245
    5267 George 0.972349
    6105 George 0.791747
    3964 John 0.898463
    4441 George 0.324026
    8394 John 0.189028
    2799 Mike 0.648241
    4185 David 0.214073
    4917 Mike 0.014573

     

    Column B can be filtered with various combinations of names.  Say we set the criteria for Alice, Boris, Mike and use the Recorder:

     

    Sub Macro1()
        Range("A1:C20").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=2, Criteria1:=Array( _
            "Alice", "Boris", "Mike"), Operator:=xlFilterValues
    End Sub

    An array gets created and the following will display the specific items in that array:

     

    Sub FilterInformation()
    Dim st As String, ws As Worksheet, rg As Range, boo As Boolean
    Set ws = ActiveSheet
    On Error GoTo GetMeOut
    Set rg = ws.AutoFilter.Range
    MsgBox rg.Address
    n = ws.AutoFilter.Filters.Count
    MsgBox n
    For i = 1 To n
        boo = ws.AutoFilter.Filters.Item(i).On
        If boo Then
            U = UBound(ws.AutoFilter.Filters.Item(i).Criteria1)
            L = LBound(ws.AutoFilter.Filters.Item(i).Criteria1)
            For j = L To U
                MsgBox ws.AutoFilter.Filters.Item(i).Criteria1(j)
            Next
        End If
        MsgBox i & Chr(10) & boo
    Next
    Exit Sub
    GetMeOut:
    MsgBox ("no filters in sheet")
    End Sub

     

    If we picked a single name, an array is not created:

    Sub Macro2()
        Range("A1:C20").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=2, Criteria1:="Alice"
    End Sub

    If we pick two names, two criteria are used, but no arrays !:

     

    Sub Macro3()
        ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=2, Criteria1:="=Alice", _
            Operator:=xlOr, Criteria2:="=Boris"
    End Sub

    Because the criteria can be either arrays or items, tests need to be performed to determine the appropriate retrieval methods.

     

    If you need more info, I suggest reposting here:

     

    http://social.answers.microsoft.com/Forums/en-US/officeprog/threads?filter=alltypes

     

    and reference this post.


    GSNU30001
    sábado, 23 de octubre de 2010 13:49
  • Thanks for that Gary's Student.  After I posted I discovered how to get at the array - the complication of the single or two values not being in an array might be resolved by interrogating 'count' - still testing.  My main problem was with dates.  I have found that by turning off the advanced display option (File tab in ribbon) called 'date grouping in filters' the problem becomes more manageable.  So my workaround is going to be detecting the option (it is a window object property) and if true (the default) I am going to exit the macro (ie give up) but if false a complex case statement could be developed to do different things for each value of the operator.

    My reason for needing this is a macro I have (developed years ago in XL2003) to take extracts of filtered tables for reports and use the filtering information to provide a string to use as a sub heading.  Those little mouse tips as you hover over the column heading would be ideal.  Now how do they do that stuff?  Must be magic :)

    Sorry I decided against the social site you recommended.  This is my first foray into 'posting' and I can't see it becoming a way of life.

    Thanks again for taking the time - Pennyowl.

    domingo, 24 de octubre de 2010 0:39
  • The mouse-over tips are Comments.  Just right-click on any cell and pick Insert Comment.
    GSNU30001
    domingo, 24 de octubre de 2010 1:54
  • Gary's Student,

    I too am very interested in this topic.  I have been using the Stephen Bullen function to display Excel 2003's Autofilter Criteria (only 2 criteria) for years and now I really want to have an equivalent for the multiple-value autofilters in Excel 2007+ - (I am using Excel 2010).

    I've tried the following but it does not work (and yes I know this would only work on more than 2 criteria):

    Function FilterCrit(rng As Range) As String

        Dim sFilter As String

        sFilter = ""

        On Error GoTo Finish

        With rng.Parent.AutoFilter

            If Intersect(rng, .Range) Is Nothing Then GoTo Finish

            With .Filters.Item(rng.Column - .Range.Column + 1)

                If Not .On Then GoTo Finish

    For i = 1 To .Count

    sFilter = sFilter & " " & .Criteria1(i) ' this is where there is an error and it jumps to Finish:

    Next

            End With

        End With

    Finish:

        FbLFilterCrit = sFilter

    End Function

    Any ideas as to why it fails????

     

    Also I believe the mouse-over reference that PennyOwl was referring to was the pop you get when you hover over the little "filter on" icon when a column is filtered for something (not a Comment pop up).  In Excel 2010 (at least) the pop up says the name of the Column in bold followed by a ":" then a new line with "Equals" followed by all the selected values in double quotes separated by commas.  Having the ability to display the contents of that pop up in a cell (via a function call) would be perfect.

     

    Curiously the Filter Pop up doesn't display the same thing in an Excel 2010 Pivot table... that would also be very useful... but one thing at at time...

    jueves, 28 de octubre de 2010 21:41
  • The help is not very on this topic - I did some experiments and you might find this table useful.  Sorry I don't know how to use these forum pages very well so it may not come out well.  For Lou Sigety's problem I think it is that any reference to Criteria1 for Operator values of 7 and above will cause an error unless it is moved into an appropriate type (array of some sort with 7 and type Interior for 8 & 12).  The dynamic filter has a number in Criteria1 that indicates the position in the various menus for selecting the filter - can't find an enumeration name for these so risky to use as they may change at any time.  The main irritation is with dates.  The grouped date selections are a total mystery but worse for me is the necessity of reformatting the criteria string if it contains a date with anything more complex than a simple "=".

    Hope that helps a bit.

     

    Filter - User Actions Data Operator Name Operator Count Cr1 VarType Criteria Type Name Criteria1 Criteria2
    Text Values/Equals/single value Text MySingleSelection 0 1 8 vbString "=No"
    Text Values/ custom OR Text xlOr 2 2 8 vbString "=No" "=Yes"
    Pick 4 values from list Text xlFilterValues 7 4 8204 vbArray ...of variants
    Custom <>space Text MySingleSelection 0 1 8 vbString <> 
    Select fill color Text xlFilterCellColor 8 1 9 vbObject Interior
    Select no fill Text xlFilterNoFill 12 1 9 vbObject Interior
    Select font color automatic Text xlFilterfFontAutomatic 13 1 3 vbLong 0
    Pick 1 value from list Text MySingleSelection 0 1 8 vbString "=X"
    Custom = one value Num MySingleSelection 0 1 8 vbString "=365"
    Pick - Select all Num MySingleSelection vbEmpty
    Choose top 4 items Num xlTop10Items 3 1 8 vbString ">=12"
    Custom between  Num xlAnd 1 2 8 vbString ">=7" "<=25"
    Choose above average Num xlFilterDynamic 11 1 3 vbLong 33
    Choose below average Num xlFilterDynamic 11 1 3 vbLong 34
    Date Values/Equals/single date Date MySingleSelection 0 1 8 vbString "=25-May-44"
    Date Values/Equals/date range Date xlAnd 1 2 8 vbString ">=40360" "<=40474"
    Date Values/After 30 Jun 10 Date MySingleSelection 0 1 8 vbString ">40359"
    All dates in period (month) Date xlFilterDynamic 11 1 3 vbLong 30
    All dates in last week Date xlFilterDynamic 11 1 3 vbLong 5
    Custom filter d1 or d2 Date xlOr 2 2 8 vbString "=21-Aug-43" "=25-May-44"
    Custom filter is after d in list Date MySingleSelection 0 1 8 vbString ">26216"
    Pick 2 dates from list Date xlOr 2 2 8 vbString "=21-Aug-43" "=25-May-44"
    Pick 3 dates from list Date xlFilterValues 7 3 8204 vbArray … of variants
    Group dates - pick 1 group Date xlFilterValues 7 0 Undef #N/A
    Group dates - pick 1 date Date xlFilterValues 7 0 Undef #N/A
    Group dates but use date value Date MySingleSelection 0 1 8 vbString ">40478"
    sábado, 30 de octubre de 2010 1:33