I've been trying to figure this out all morning. Is there a way to loop through the criteria in a column filter in Excel, while filling an array with the names of the criteria? It seems that filter objects only have a criteria1 and criteria2. Sorry if any of my terminology is unclear, I'm pretty new to Excel.
(this is using Excel 2007)
It appears that if you have 1 or 2 criteria selected then these will be stored in .Criteria1 and .Criteria2.
However if you have more than 2 then .Criteria1 becomes an array of the selected filters - which you can then iterate through using...
Dim iFilt As Integer 'This should be set equal to the column
'index you are interested in
Dim iFiltCrit as Integer
For iFiltCrit = 1 To UBound( Sheet1.AutoFilter.Filters(iFilt).Criteria1 )
Debug.Print Sheet1.AutoFilter.Filters(iFilt).Criteria1(iFiltCrit)
Next
Note that this only works for a column which has more than 2 criteria - how you determine if that is the case or not is up to you - it's a rather clunky piece of Excel API (surprise surprise) as it is loosely typed.
Dim iFiltCrit As Integer Debug.Print (UBound(ActiveSheet.AutoFilter.Filters(projCol).Criteria1)) For iFiltCrit = 1 To UBound(ActiveSheet.AutoFilter.Filters(projCol).Criteria1) Debug.Print (ActiveSheet.AutoFilter.Filters(projCol).Criteria1(iFiltCrit)) Next The Ubound has a value of 4, but it's giving an error that states "Property let procedure not defined and property get procedure did not return an object"
I had the same problem, where I wanted to have a user-defined string for filtering, in which people can give the column and the filtervalue for multiple columns at once.
In this example the string users can define looks like:
"A,foo;B,bar;AD,beyond all recognition"
Or
"ColumnLetterOrIndex comma FilterValue Semicolon" Repeat.
and so on for any number of columns and values.
Code:
Public Function createFilter(filterstring as string) as Variant
Dim tempFilter As Variant
Dim realFilter As Variant
tempfilter = Split(filterstring, ";")
For i = LBound(tempfilter) To UBound(tempfilter)
ReDim Preserve realFilter(i)
realFilter(i) = Split(tempfilter(i), ",", 2)
'The 2 is needed if the filtervalue contains a comma on itself. Otherwise, it can be omitted.
Next i
createFilter = realFilter
End Function
The above function will create a multidimensional array from a string of which the values can be used with Excels autofilter from a string. Usage code:
Dim userFilter as Variant
userFilter = createFilter(userDefinedFilterString)
For i = LBound(userFilter) To UBound(userFilter)
'Note that here you'll have to convert the Columnletter to something numeral, unless the users define their column like 1, 2, 3 instead of A, B, C.
thefiltercolumn = Measure.filter(i)(0)
.AutoFilter thefiltercolumn, Measure.filter(i)(1)
Next i
When the filtervalues are equal to the "Is not equal to" or "contains", you can use excel's built-in way of doing that, e.g.
A,<>foo;B,*bar*;AD,>=5
Bad part: Multiple criteria on 1 column is not available like in the previous example.
