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:
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:
To filter the pies to just berry you have to load their names into an array and set Criteria1 and Operator like:
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, _
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:
Here 8643294 is the RGB value of that greenish color. In order to determine that RGB in code, you need to use something like:
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:
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:
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:
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:
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:
lo.DataBodyRange.AutoFilter field:=4, _
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.
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.