CODEWITHSUNDEEP
excelvbaloops
superadamwo
superadamwo

Reputation: 63

VBA: Loop through criteria in an Excel filter?

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)

Upvotes: 2

Views: 10252

Answers (2)

R. Sportel
R. Sportel

Reputation: 11

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.

Upvotes: 1

El Ronnoco
El Ronnoco

Reputation: 11922

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.

Upvotes: 1

Related Questions