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, _
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:
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:
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:
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:
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:
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:
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, _
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.
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).
You do love your pivot tables! As well you should.
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:
One more alternative is:
1, "*berry*"
You forgot/overlooked:
‘ all lastmonth: criterion 8, operator 11
Thanks! Not to mention 17 to 20, the QuarterPeriods. Like I said it was tedious to copy them, but you’d think I would have done a count. They’re fixed now.
Pingback: Excel Roundup 20150302 « Contextures Blog
You inspired me to translate this to VBA:
http://www.snb-vba.eu/VBA_Autofilter_en.html
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.
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.
'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
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.
Hmmm. Works on my computer running Excel 2010 and Windows 8.1.
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
Hey Shawn, I’d try asking this question at Stack Overflow or another forum. Sorry, but I don’t have time to offer more than that.
Good luck.
Thanks Doug, your help and response is appreciated.
Shawn
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.
Thanks Jeff, for your response. I have emailed you the test file.
Shawn
Whoops, I meant to say ‘Advanced Filter’ and not ‘Auto Filter’. The Advanced Filter will let you filter on any number of complicated expressions, so I suggest you give Google a spin on ‘Advanced Filter’ or check out this great article from Deb Dalgleish at http://www.Contextures.com:
http://www.contextures.com/xladvfilter01.html
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?
Thanks, will give both your suggestions a try: google & advanced filter.
Shawn
Trying to make something work and needing help.
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.
Thank you. I kept missing the DataBodyRange property. Your very helpful site clued me in. I was able to write VBA code to change the autofilter for a list of filter values set in a cell (why do this? I update the cell based on a hyperlink selected in another sheet). Here is a working example.
‘ Worksheet change event for 800-53 controls worksheet
‘
‘ check to see if the control cell changed. If so, update the table filter to match each selection in the list in the control cell.
‘ one item is easy
‘ multiple items should be separated by commas
‘ blank value selects all (not done)
‘ special cases to be handled later (for instance regular expression selections, or lookup expressions) (not done)
‘
‘ dependencies: constants in Initialization module
‘
Private Sub Worksheet_Change(ByVal Target As Range)
Dim newString As String
Dim partsOfString() As String
Dim eachSubString As Variant
Dim VariantArr() As Variant
Dim lo As Excel.ListObject
If Not Intersect(Range(Target.Address), Worksheets(CNnistWS).Range(CNctrlS)) Is Nothing Then
newString = Range(CNctrlS).Text
partsOfString() = Split(newString, “,”)
For Each eachSubString In partsOfString
eachSubString = Trim(eachSubString)
Next eachSubString
VariantArr = WorksheetFunction.Index(partsOfString, 1, 0) ‘https://stackoverflow.com/questions/37515352/how-to-convert-string-type-array-to-variant-type-array-excel-vba
Set lo = Worksheets(CNnistWS).ListObjects(1)
lo.DataBodyRange.AutoFilter Field:=2, Criteria1:=VariantArr, Operator:=xlFilterValues
End If
End Sub
Great article!
I can generate all of the Enums 1-11 using code such as tbl.AutoFilter.Filters(colno).Operator
with the exception of the seemingly basic 7 i.e. xlFilterValues.
I naively thought that this was simply a filter of a numeric field where the condition was a “simple” one i.e. no xlAnd or xlOr. I’m using an Exel Table, but have also tested in an autofiltered range – same thing.
What am I missing? The MS doc is brief, which is fine when things work as expected 🙂
Hi Andrew, glad you like it!
Here’s my observation of the Operators returned by your formula based on the number of items I select in a table’s dropdown filter.
One item: Operator 0
Two items: Operator 2 (xlOr)
Three or more items: Operator 7 (xlFilterValues)
So xlFilterValues is like an xlOr for more than two items. This is at least indirectly discussed at the beginning of the post.
Let me know if that helps.
Perfect – I should have thought of that really. But thanks very much
Worksheets("comparison").Range("$A$1:$J$5000").AutoFilter Field:=10, Criteria1:="0", _
Operator:=xlAnd
Worksheets("comparison").Range("$A$1:$J$5000").AutoFilter Field:=2, Criteria1:= _
"*Korea*", Operator:=xlAnd
Unload Me
End Sub
Private Sub OptionButton2_Click()
Worksheets("comparison").Range("$A$1:$J$5000").AutoFilter Field:=10
Worksheets("comparison").Range("$A$1:$J$5000").AutoFilter Field:=2
Unload Me
End Sub
I have filter like this and the result is coming in sheets.
On the same time, i want one listbox which should display the same filter data.. how to code this.
I have fixed j5000 but it may be anything says 10 or 1000.
Hi C K,
Sorry, I don’t have time to answer your question. I would consider asking on Stack Overflow or another forum. Good luck!