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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
810
Office Version
  1. 365
Platform
  1. Windows
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_RegionOptIn_NH_LandMailLast_BookedLast_BookedFamilyFunFamilyFunUKUKSportsSportsEventsEventsEUEUShowtimeShowtimeOptIn_ThirdPartyOptIn_DirectEmailOptIn_MktgEmail
<>0<>0>=0<=1000000>=1<=10000=FamilyFun
<>0<>0>=0<=1000000>=1<=10000FamilyFun

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:

VBA Code:
    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:

Excel Formula:
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
FYI, I would suggest you don't use TypeOf to look for checkboxes specifically, since it will also include OptionButtons and ToggleButtons in the results. Use Typename(ctrl) = "CheckBox" instead.

Looking at your criteria, I think you probably want to use a formula for that in the criteria range, or you could probably do what you've shown with an autofilter and one additional formula column in your data to return the OptIn type.
 
Upvote 0
Hi Rory,

Cheers I'll make that change. So far the result of this

VBA Code:
Dim Ctrl As Control
Dim RegString As String
RegString = ""
For Each Ctrl In CritPanel.RegionFrame.Controls
    If TypeName(Ctrl) = "CheckBox" Then
        If Ctrl.Value = False Then
            RegString = RegString & Left(Ctrl.Name, 2)
        End If
    End If
Next

Gives me something like "A6B3B4C3D1"

So five regions. I'm thinking of comparing the string in the Brochure_Region column with the string, if it's in the string, then mark it as a 0, but I can foresee this taking forever with 300,000 rows.


Can you help me or point me towards understanding more about using formulas in the Advanced Filter criteria? Thanks.
 
Last edited:
Upvote 0
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)))
 
Upvote 0
Solution
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)))
Magic.

And again it's super fast at calculating.

Thanks again.
 
Upvote 0
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)))

Hm.

Here's an interesting problem.

Here's the VBA which places the formula:

VBA Code:
"=AND(DataFeed!RC18<>"""",ISERROR(MATCH(DataFeed!RC18,R2C27:R5C27,0)))"


So, DataFeed Column R is where the Regions are held.

And Column AA, 2:5 is where the regions list is (this is altered using lastrow btw)

If I have just one criteria on the sheet, like this:

Brochure_RegionOptIn_NH_LandMailLast_BookedLast_BookedFamilyFunFamilyFunUKUKSportsSportsEventsEventsEUEUShowtimeShowtimeOptIn_ThirdPartyOptIn_DirectEmailRegionFormula
<>0<>0>=0<=1000000>=1<=10000=UK
FALSE​

It's fine, because it will place the "RegionFormula" in the second row, and that relates to the second row in Datafeed, right?

But, if I have two sets of criteria for the advanced filter, like this:


Brochure_RegionOptIn_NH_LandMailLast_BookedLast_BookedFamilyFunFamilyFunUKUKSportsSportsEventsEventsEUEUShowtimeShowtimeOptIn_ThirdPartyOptIn_DirectEmailRegionFormula
<>0<>0>=0<=1000000>=1<=10000=UK
FALSE​
<>0<>0>=0<=1000000>=1<=10000=Sports


I get Regions included (A6, B3, C1, D1) that I have excluded, because those regions apply to "Sports", right?

But if I apply this to the range vertically, as below:

Brochure_RegionOptIn_NH_LandMailLast_BookedLast_BookedFamilyFunFamilyFunUKUKSportsSportsEventsEventsEUEUShowtimeShowtimeOptIn_ThirdPartyOptIn_DirectEmailRegionFormula
<>0<>0>=0<=1000000>=1<=10000=UK
FALSE​
<>0<>0>=0<=1000000>=1<=10000=SportsFALSE

The second false will start looking from row 3 onwards. So how do I make this always look at Row 2, without locking it to cell 2?

VBA Code:
"=AND(DataFeed!RC18<>"""",ISERROR(MATCH(DataFeed!RC18,R2C27:R5C27,0)))"


Cheers.
 
Upvote 0
Use the A1 style Formula, not FormulaR1C1.

Looking at this makes me think your data layout is probably not ideal and/or you should be using a database.
 
Upvote 0
Use the A1 style Formula, not FormulaR1C1.

Looking at this makes me think your data layout is probably not ideal and/or you should be using a database.

Thanks, this is working now. I did have problems with it not filtering that column as it should but that's because I didn't lock the rows on the list of exclusions.

What do you mean about a database? We have a non-ideal relationship with Excel at this company as they don't want the expenditure on additional software and maintenance, so we Excel absolutely everything.

Purpose of this is to take an extract of our clients from the database and chop them down based on several different and complex criteria. It's far easier for a layman to open a control panel and flick some buttons than it is to perform an extremely complex SQL extract which is then difficult to validate and re-run.

It's currently working really well, but there are just new things and problems that keep cropping up which are throwing me through a loop so it has been extremely helpful to receive regular assistance.
 
Upvote 0
FYI, I would suggest you don't use TypeOf to look for checkboxes specifically, since it will also include OptionButtons and ToggleButtons in the results. Use Typename(ctrl) = "CheckBox" instead.

Looking at your criteria, I think you probably want to use a formula for that in the criteria range, or you could probably do what you've shown with an autofilter and one additional formula column in your data to return the OptIn type.

Hi Rory,

I believe this is all related and I'm reluctant to start a new thread for each of the little problems I have.

This code is working fine, but it's a little slow. It takes about 0.5 seconds to apply and then count the criteria, for example how many FamilyFun clients there are, etc.

I've now got the Region exclusions working thanks to you and I want to also count the amount of each region. The below code does this..

In the RegionFrame, I have 14 Checkboxes and 14 textboxes with a count of the volumes in. When I execute the following code, it takes about 4-5 seconds to compute, which is about 8x slower than if the code wasn't in there.

VBA Code:
' Count Filtered Region Volumes
Dim Ctrl2 As Control
Dim reg As String

CritPanel.RegVolA1.Enabled = False
CritPanel.RegVolA2.Enabled = False
CritPanel.RegVolA3.Enabled = False
CritPanel.RegVolA4.Enabled = False
CritPanel.RegVolA5.Enabled = False
CritPanel.RegVolA6.Enabled = False
CritPanel.RegVolB1.Enabled = False
CritPanel.RegVolB2.Enabled = False
CritPanel.RegVolB3.Enabled = False
CritPanel.RegVolC1.Enabled = False
CritPanel.RegVolC2.Enabled = False
CritPanel.RegVolC3.Enabled = False
CritPanel.RegVolD1.Enabled = False
CritPanel.RegVolD2.Enabled = False

For Each Ctrl In CritPanel.RegionFrame.Controls
    If TypeName(Ctrl) = "CheckBox" Then
        If Ctrl.Value = True Then
            reg = Left(Ctrl.Name, 2)
            For Each Ctrl2 In CritPanel.RegionFrame.Controls
            If TypeName(Ctrl2) = "TextBox" Then
                If Right(Ctrl2.Name, 2) = reg Then
                Ctrl2.Enabled = True
                Range("AB2").FormulaR1C1 = "=SUMPRODUCT((DataFeed!R2C18:R302868C18=""" & reg & """)*(SUBTOTAL(103,OFFSET(DataFeed!RC18,ROW(DataFeed!R2C18:R302868C18)-MIN(ROW(DataFeed!R2C18:R302868C18)),0))))"
                Ctrl2.Value = Format(Range("AB2").Value, "#,##0")
                End If
            End If
            Next
        End If
    End If
Next
Range("AB2").Value = ""

I'm not sure if it's because I'm running two For Each loops inside each other, or if it's the sumproduct formula calculating.

Basically what I'm trying to do here is loop through each checkbox. If the Checkbox is ticked (not excluded), it then saves the value of the Checkbox name (A1_Checkbox, A2_Checkbox etc) and loops through the RIGHT(name, 2) of the textbox to find the corresponding one, so A1_Checkbox corresponds to TextboxA2

When it finds a corresponding checkbox, it places the formula to count the amount of times the region appears in the filtered list in cell AA2, and then passes that figure into the value of the checkbox as I can't figure out a way to transpose that formula into VBA efficiently.


Can you think of a way to speed it up and am I doing anything unnecessary? Thanks.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,210
Members
453,023
Latest member
alabaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top