Autofilter VBA Operator Parameters

Autofilter VBA Operator Parameters

The other day I was flipping back and forth between two tables with related sets of data and comparing the rows for one person in each set. I do this kind of thing all the time and often end up filtering each table to the same person, location, date, or whatever, in order to compare their rows more easily. I thought “Wouldn’t it be great to write a little utility that filters a field in one table based on the same criteria as a filter in another table?” Like many questions that begin with “Wouldn’t it be great to write a little utility” this one led me on a voyage of discovery. The utility isn’t finished, but I know a lot more about Autofilter VBA operator parameters.

My biggest discovery is that the parameters used in the Range.Autofilter method to set a filter don’t always match the properties of the Filter object used to read the criteria. For instance, you filter on a cell’s color by passing its RGB value to Criteria1. But when reading the RGB value of a Filter object, Criteria1 suddenly has a Color property. More about this and some other such wrinkles as we go along.

I also realized there are a bunch of xlDynamicFilter settings – like averages and dates – that all use the same operator and are specified by setting the Criteria1 argument.

I also noticed a typo in the xlFilterAllDatesInPeriodFebruray operator. I wonder if anybody has ever even used it?

The Basics – Setting a Filter in VBA

To review the basics of setting Autofilters in VBA you can peruse the skimpy help page. To add a bit to that, let’s return to one of my favorite tables:

Pie Table

Its columns have several characteristices on which you can filter, including text, dates and colors. Here it’s filtered to names that contain “A”, pies with the word “Berry”, dates in the current month and puke-green:

Pie Table Filtered

To filter the pies to just berry you have to load their names into an array and set Criteria1 and Operator like:

Sub PieTableFilters()
Dim lo As Excel.ListObject
Dim BerryPies As Variant

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
BerryPies = Array("Strawberry", "Blueberry", "Razzleberry", "Boysenberry")
lo.DataBodyRange.AutoFilter field:=2, _
    Criteria1:=BerryPies, Operator:=xlFilterValues
End Sub

Discovery #1 – An xlFilterValues Array Set With Two Values Becomes an xlOR Operator:
To read the filter created by the code above, I’d still use the xlFilterValues operator and read the array from Criteria1. However, if the column was filtered to only two types of pies, I’d use the xlOR operator and Criteria1 and Criteria2. This is unchanged since Johh Walkenbach posted about Stephen Bullen’s function to read filters settings for Excel 2003.

Similarly, if I set a filter with a one-element array, e.g., only one type of pie, and then read the filter operator it returns a 0.

The Basics, Continued – Setting a Color Filter

Here’s how to set the filter for the last column. It’s based on cell color. Note that it works for Conditional Formatting colors as well as regular fill colors:

lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=8643294, Operator:=xlFilterCellColor

Here 8643294 is the RGB value of that greenish color. In order to determine that RGB in code, you need to use something like:

Set lo = ActiveWorkbook.Worksheets("pie orders").ListObjects(1)
Debug.Print lo.AutoFilter.Filters(4).Criteria1.Color

Discovery #2 – The Filter.Criteria1 Property Sometimes has Sub-Properties, Like Color:
Note that the RGB isn’t in Criteria1 – it’s in Criteria1.Color, a property I only discovered by digging around in the Locals window:

https://i0.wp.com/yoursumbuddy.com/wp-content/uploads/2015/02/Post_0076_cell_color_in_locals_window.jpg?resize=584%2C526

Also note that there are a bunch of other properties there, like Pattern, etc.

Further, if a column is filtered by conditional formatting icon (yes you can do that) using the xlFilterIcon(10) parameter then Criteria1 contains an Icon property. This property is numbered 1 to 4 (I think) and relates to the position of the icon in its group.

Discovery #3: xlDynamicFilter and Its Many Settings
The xlFilterDynamic operator, enum 11, is a broad category of settings. You narrow them down in Criteria1. So, for instance, you can filter a column to the current month like:

lo.DataBodyRange.AutoFilter field:=3, _
    Criteria1:=XlDynamicFilterCriteria.xlFilterThisMonth, _
    Operator:=xlFilterDynamic

This is a good time to mention the chart below that contains all of these xlDynamicFilter operators and their enums. Looking at it you’ll note that in addition to a whole lot of date filters (all of which appear in Excel’s front end as well) there’s also the Above Average and Below Average filters.

Discovery #4: Top 10 Settings Are Weird

To set a Top 10 item in VBA you do something like this:

lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=3, Operator:=xlTop10Items

Here, I’ve actually filtered column 4 to the top three items (As stated in Help, Criteria1 contains the actual number of items to filter). If I look at the filter settings for that column in the table I’ll see that Number Filters is checked and the dialog shows “Top 10.” That makes sense.

However if we look at the locals window right after the line above is executed, we see that the number 3 which we coded in Criteria1 is replaced by a greater than formula:

Top 3 in locals window

Further, if I then wanted to apply this filter to another column based only on what I’m able to read in VBA, I’d have to change it to:

'ValuePulledFromAnotherFilter contains ">=230"
lo.DataBodyRange.AutoFilter field:=4, _
    Criteria1:=ValuePulledFromAnotherFilter

Note that I don’t use an operator at all. We could also use xlFilterValues or xlOr. Using nothing best reflects what shows in the locals window, where the operator changes to 0 after the code above is executed.

The Chart

Below is a chart, contained in a OneDrive usable and downloadable workbook, that summarizes much of the above. It includes all the possible Autofilter VBA Operator parameters, as well as all the sub-parameters for the xlFilterDynamic operator.

Discovery #5: February is the Hardest Month (to spell)

I like to automate things as much as possible, so generating the list of xlFilterDynamic properties was kind of a pain. To get the list of constants from the Object Browser I had to copy and paste one by one into Excel, where I cobbled together a line of VBA for each one, like:

Debug.Print “xlFilterAboveAverage: ” & xlFilterAboveAverage

Of course I saw I didn’t need to paste all the month operators into Excel since I could just drag “January” down eleven more cells and append the generated month names. Minutes saved!

But when I ran the code it choked on February. It was then I noticed that its constant is misspelled.

Two More Tips

1. Use Enum Numbers, not Constants: This misspelling of February points out a good practice when using any set of enums: use the number, not the constants. For example, use 8, not xlFilterValues. This helps in case the constants are changed in future Excel versions, and with international compatibility, late binding and, at least in this case, spelling errors. Of course it’s not nearly as readable and you have to figure out the enums.

2. The Locals Window is Your Friend: I don’t use the Locals window very much, but in working with these Autofilter settings, it was hugely helpful. It revealed things that I don’t think I’d have figured out any other way.

Conclusion – Building a Utility

Armed with my newfound knowledge, I could come close to building a utility that copies filters from one table to another, or one like the Walkenbach/Bullen filter lister linked at the beginning of this post. In most cases I could just transfer the filter by using the source Operator and Criteria1 (and Criteria2 where applicable)

But as we’ve seen, there’s a couple of filter types where the operator and/or criteria change. The worst of these is a Top 10 filter. My first thought was to count the number of unfiltered cells, but if the column has duplicates, e.g., your top value appears three times, that won’t always be accurate. In addition, other columns could be filtered which could also throw off the count.

Transferring a color scale filter would be even trickier as it’s very possible the same color wouldn’t exist in the second table.

Whew! Long post! Leave a comment if you got this far and let me know what you think.

23 thoughts on “Autofilter VBA Operator Parameters

  1. “Wouldn’t it be great to write a little utility that filters a field in one table based on the same criteria as a filter in another table?”

    That kind of work is perfect for PivotTables. I’d make two PivotTables, and hook them up via my dictionary based code that syncs pivots on different caches pretty efficiently.

    http://blog.contextures.com/archives/2013/06/18/update-multiple-pivot-tables-20130618/

    (That code is due for a tidyup, and I’m about to do it).

  2. Although ‘Intelli’sense shows ‘autofilter’ after typing Sheet1.ListObjects(1). it errors out when running the code

    Sheet1.ListObjects(1).Autofilter 1, “*berry*”

    equivalent alternatives are:

    Sheet1.ListObjects(1).Range.Autofilter 1, “*berry*”
    Sheet1.ListObjects(1).DataBodyRange.Autofilter 1, “*berry*”

    • That’s true. It’s works as a property, but not as a method. So this works:

      Debug.Print Sheet1.ListObjects(1).Autofilter.Filters.Count

      One more alternative is:

      Sheet1.ListObjects(1).Listcolumns(1).DataBodyRange.AutoFilter _
          1, "*berry*"
  3. Pingback: Excel Roundup 20150302 « Contextures Blog

    • Wow, that’s great! Another amazing resource on your website.

      I”m glad to see you continued with the pie theme :).

      FYI, I linked to it at the Contextures Roundup with the pingback above.

  4. This code will take the Excel calculated result for a filter and perform a binary search for a filter input value that produces the same result. The input value found may not be the same as the user entered, because there could be multiple values that produce the same result.

    Option Explicit
    'Find user inputted filter number by binary searching
    Function FilterAgrumentSearch(sheetName As String, filterColumnRng As Range, ByVal nMaximum As Long) As Long 'closest result
        Dim nMinimum As Long
        Dim filterColumn As Long
        Dim operatorNumber As Long
        Dim actualCriteria1String As String, actualCriteriaNumber As Long
        Dim iterativeCriteria1String As String, interativeCriteriaNumber As Long
       
        nMinimum = 1
       
        With Sheets(sheetName)
            'find filter heading cell
            filterColumn = filterColumnRng.Column
           
            With .AutoFilter.Filters(filterColumn)
                operatorNumber = .operator 'get operator number
                actualCriteria1String = .Criteria1 'get actual criteria1 calculated information
            End With
           
            actualCriteriaNumber = CLng(Right(actualCriteria1String, Len(actualCriteria1String) - 2)) 'extract numeric part of conditional

            'iterate to a solution (NOTE: There will often be a range of solutions that give the same Criteria1 string!)
            Dim nMiddle As Long
            Select Case operatorNumber
                Case xlBottom10Items, xlBottom10Percent 'criteria 'actual' versus 'iterative" are reversed from Top operation instances
                    Do
                        nMiddle = Round(nMinimum + (nMaximum - nMinimum) / 2)
                       
                        'run filter with test criteria
                        filterColumnRng.AutoFilter Field:=filterColumn, Criteria1:=nMiddle, operator:=operatorNumber 'set filter
                       
                        'extract numeric part of filter run
                        iterativeCriteria1String = .AutoFilter.Filters(filterColumn).Criteria1
                        interativeCriteriaNumber = CLng(Right(iterativeCriteria1String, Len(iterativeCriteria1String) - 2)) 'extract criteria number
                       
                        If actualCriteriaNumber < interativeCriteriaNumber Then 'too high of a result
                            nMaximum = nMiddle - 1
                        ElseIf actualCriteriaNumber > interativeCriteriaNumber Then 'too low of a result
                            nMinimum = nMiddle + 1
                        Else 'equal so a solution was found
                            GoTo setResultAndExit
                        End If
                    Loop While nMinimum <= nMaximum 'continue search
                Case xlTop10Items, xlTop10Percent 'criteria 'actual' versus 'iterative" are reversed from Top operation instances
                    Do
                        nMiddle = Round(nMinimum + (nMaximum - nMinimum) / 2)
                       
                        'run filter with test criteria
                        filterColumnRng.AutoFilter Field:=filterColumn, Criteria1:=nMiddle, operator:=operatorNumber 'set filter
                       
                        'extract numeric part of filter run
                        iterativeCriteria1String = .AutoFilter.Filters(filterColumn).Criteria1
                        interativeCriteriaNumber = CLng(Right(iterativeCriteria1String, Len(iterativeCriteria1String) - 2)) 'extract criteria number
                       
                        If actualCriteriaNumber > interativeCriteriaNumber Then 'too low of a result
                            nMaximum = nMiddle - 1
                        ElseIf actualCriteriaNumber < interativeCriteriaNumber Then 'too high of a result
                            nMinimum = nMiddle + 1
                        Else 'equal so a solution was found
                            GoTo setResultAndExit
                        End If
                    Loop While nMinimum <= nMaximum 'continue search
                Case Else
                    Debug.Assert False 'shouldn't get here
            End Select

            'Should never get here! So something is not processing correctly.
            MsgBox "Failed to find value that produces exact Crtieria1 result!" _
            & vbCrLf & "Actual " & actualCriteria1String & ", Iterated " & Left(actualCriteria1String, 2) & interativeCriteriaNumber _
            & vbCrLf & "Using value " & nMiddle, vbCritical
        End With
       
    setResultAndExit:
        FilterAgrumentSearch = nMiddle 'criteria1 value that produced same criteria result
    End Function
  5. Thanks StevenG. I tried to run this for ten minutes or so and got a couple of Type Mismatch runtime errors early on in the code. Some instructions/examples would help people who want to run this.

  6. Hi Doug

    I have a problem coding for a dynamic excel filter. I have the following sample excel database in sheet “data”
    Name Dept Amt
    a p 1
    a q 2
    a r 3
    a s 4
    b s 5
    b r 6
    b p 7
    b q 8
    c p 9
    c q 10
    c r 11
    c s 12
    a s 13
    a r 14
    a p 15
    a q 16
    b p 17
    b q 18
    b r 19
    b s 20
    c s 21
    c r 22
    c p 23
    c q 24

    In another sheet (“condt”), I place my conditions which are user defined, to be processed at runtime. Eg.
    Fld Search val
    Name a
    Dept p, r
    Amt >10

    My macro below processes the above filter conditions dynamically. (I’m not sure I need Criteria 2 as I can separate multiple conditions with a comma – refer dept = p, r). I will later like to put the sample 3 filter options in the macro below within a loop, but the problem: how do I change the “Operator” in my code dynamically to suit all/every type/kind of user defined search input (>10; >=01-Mar-2015, <10-Mar-2015; etc.) in sheet "condt"?

    (Presently, Operator:=xlFilterValues works for the 1st 2 filters and Operator:=xlAnd works for the 3rd condition in sheet vcondt) The macro is under:

    Sub Fltr()
    Dim trows As Integer, tcols As Integer, vfld As String, vcondt As Variant, FilterField As String
    Dim rng As Range

    vfld = Sheets("condt").Range("B2").Value
    vcondt = Split(Sheets("condt").Range("C2").Value, ",")
    trows = Sheets("data").UsedRange.Rows.Count + 1
    tcols = Sheets("data").UsedRange.Columns.Count

    Set rng = Sheets("data").Range(Cells(2, 1), Cells(trows, tcols))
    FilterField = WorksheetFunction.Match(vfld, rng.Rows(1), 0) 'hdr row

    If ActiveSheet.AutoFilterMode = False Then rng.AutoFilter 'Turn on filter if not already turned on
    rng.AutoFilter Field:=FilterField, Criteria1:=vcondt, Operator:=xlFilterValues

    '——— apply 2nd filter
    vfld = Sheets("condt").Range("B3").Value
    vcondt = Split(Sheets("condt").Range("C3").Value, ",")
    FilterField = WorksheetFunction.Match(vfld, rng.Rows(1), 0) 'hdr row
    rng.AutoFilter Field:=FilterField, Criteria1:=vcondt, Operator:=xlFilterValues

    '——— apply 3rd filter
    vfld = Sheets("condt").Range("B4").Value
    vcondt = Split(Sheets("condt").Range("C4").Value, ",")
    FilterField = WorksheetFunction.Match(vfld, rng.Rows(1), 0) 'hdr row
    rng.AutoFilter Field:=FilterField, Criteria1:=vcondt, Operator:=xlAnd

    End Sub

    Thanks in advance

    Shawn

  7. Shawn: I imagine you’d have to use the Autofilter for that. This can be pretty tricky…especially if you want to support the operator too. I have some code to filter a table based on multiple conditions that I’m working on. If you flick me a line at weir.jeff@gmail.com I can flick it through.

    At some stage this code is going into the book that I’m in the process of writing, and will also ship with a commercial add-in I’m putting together. So I’m not quite ready to share it widely yet.

      • Also, it looks like a PivotTable will easily accomplish what you are after, with a minimum of VBA. (And in fact you can actually do the kind of filtering you’re after without any VBA if you use a PivotTable, although users who aren’t familiar with PivotTables might need some instructions)

        What is the purpose of filtering the Table? After it is filtered, what will you do with it?

  8. Trying to make something work and needing help.

     ActiveSheet.Range("$A$1:$BH$2600").AutoFilter Field:=41, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

    On the same column I also need to filter the blanks.
    When I try to add a second criteria it only filter Blanks or LastMonth.
    But really need them both.
    Can you help?

    • Hi fatae. I’m not able to combine those two types of filters in Excel, so I don’t think I can do it in VBA either. You could ask on SuperUser or another forum if it’s possible to do such a filter in the front end.

Leave a Reply to short cuts Cancel reply

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>