Possible ways to pass variable list into an Advanced Filter, or created helper column?

[RockandGrohl]

Hello, I have a userform that has 16 selectable checkboxes for region names (A1, A2, A3 etc) and I would like the result of these checkboxes to be applied to an Advanced Filter, the criteria of which is built dynamically.

Here is what my advanced filter criteria looks like:

Brochure_Region

OptIn_NH_LandMail

Last_Booked

Last_Booked

FamilyFun

FamilyFun

UK

UK

Sports

Sports

Events

Events

EU

EU

Showtime

Showtime

OptIn_ThirdParty

OptIn_DirectEmail

OptIn_MktgEmail

<>0

<>0

>=0

<=1000000

>=1

<=10000

=

FamilyFun

<>0

<>0

>=0

<=1000000

>=1

<=10000

FamilyFun

Brochure Region is what I am filtering, and no matter what this must always exclude 0's.


Let's say of the 16 Regions, 4 are unticked. And these are A1, B1, C1 and D1.

It would be helpful if I could do something like this

Brochure_Region

<>0, <>A1, <>B1, <>C1, <>D1

<>0, <>A1, <>B1, <>C1, <>D1

Unfortunately, I do not think this particular is possible.

The way I see it, there's two problems here. One is that I need to get a complete list of what remains checked or unchecked. I understand the best way to do this would be to run through the frame and look at every checkbox, such as in this helpful post by Haluk on this forum:

    Dim Ctrl As Control
    For Each Ctrl In Me.Frame1.Controls
        If TypeOf Ctrl Is MSForms.CheckBox Then
            If Ctrl.Value = False Then
                MyMsg = MyMsg & vbCrLf & Ctrl.Name & " is not selected"
            End If
        End If
    Next
    If MyMsg = Empty Then
        MsgBox "All of them are selected"
    Else
        MsgBox MyMsg
    End If

That then gives me a list of all excluded clients, which I can pass into some kind of peverse search array similar to this:

Range("somewhere").FormulaArray = "=COUNT(SEARCH({"", " & Excluded1 & ","","", " & Excluded2 & ","","", " & Excluded3 & ","","", " & Excluded4 & ","","", " & Excluded5 & ","","", " & Excluded6 & ","","", " & Excluded7 & ",""},"", ""&RC[-14]&"",""))"

(this is from something else, but I could modify it to suit my needs, for example removing the quotes and commas which aren't necessary here)

However from experience this is quite slow. But it would show >0 if any excluded regions are contained in the Brochure Region cell, and then I would filter this new row to only show 0's which means their region doesn't exist.


There's got to be an alternative way for this to work, however. Any ideas? Thanks.

 

[RockandGrohl]

I think you should start a new thread. Although it's the same project, this is not the same question you started with. (You can always add a link back to this thread for context).

I do worry about filling up this forum. These are all novel problems for me though, totally new ground and I feel like some of the problems I'm having are more complex than I can put in to Google to parse.

 

[RockandGrohl]

To use a formula in the advanced filter criteria, you need a header cell that does not match any of the actual data headers (or leave it blank) and then the formula simply refers to the first cell in one or more data columns to do its calculation. When the filter evaluates the formula, it will iterate down the rows and replace each row reference in the formula with the next row, evaluate and repeat. The rows that are left visible are the ones where the formula evaluates to True.

So let's say the brochure region column is column A with headers in row 1 and data starting in row 2. To match the data against a list of regions in cells Z1:Z4 for example, you'd use:

=ISNUMBER(MATCH(A2,$Z$1:$Z$4,0))

When the filter is applied, it will match A2 against the list, then A3 against the list and so on, and only leave visible the ones that match. To exclude that list, and blanks, you'd change the formula to:

=AND(A2<>"",ISERROR(MATCH(A2,$Z$1:$Z$4,0)))

Hi Rory,

Can I just clarify - does this formula need to be input into each line of the advanced filter? Say I have fifteen OR conditions, do I need to repeat the formula 15 times? Or just in range 'X' 2 (where X is whatever column it goes in)

Cheers.

 

[RoryA]

If you're combining it with other criteria, then the same rules apply - i.e. if you want to AND it with another set of criteria, it needs to be on every row where that is the case. Alternatively, combine all the criteria into one formula.