I can count the results of a filter based on criteria, but it's slow. Can anyone help?

[RockandGrohl]

I'm using a SUMPRODUCT in combination with OFFSET and ROW to count the result of a filter, in order to tell me how many customers belong to each region.

If I exclude this snippet of code, where the regions are counted, the code executes in approximately half a second, which is ideal. It's implementing all the various user-input criteria of a database of >300,000 and then returning almost immediately a list of clients with the criteria applied.

However, including the below to count each of the 14 applicable regions results in the code executing in 5 seconds, roughly 10 times longer.

Can it be sped up?

' 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")
                Range("AB2").Value = ""
                End If
            End If
            Next
        End If
    End If
Next

As an explainer, in the RegionFrame, I have 14 checkboxes and 14 textboxes.

Any combination of these 14 checkboxes can be ticked, so I need to loop through each checkbox to find a checked one. Then I get the name of the region it corresponds to - "B3_Checkbox" gives me "B3" as a string.

I then check this against the name of the textboxes until it matches, so when it finds "VolumeB3" I know I have the right box. At this point, I place the formula in AB2 on the temporary sheet to count the number of filtered results that belong to Region "B3", and then pass the result of that into the textbox that corresponds with the checkbox.


Just a note, as part of the filtering process, if I uncheck a checkbox those regions are filtered out to be excluded.


I hope that's clear and there's an innovative solution. It's not the end of the world if it takes longer to execute but part of the charm of this process has been near-instant updating of criteria selections via a userform.

 

[Alex Blakenburg]

If you are prepared to use a helper column to determine whether a row is hidden or not, then I imagine you could use countifs rather than sumproduct and that should be slightly quicker or for the fastest solution still using the helper column, have a pivot summarising all regions and either only showing only the filtered lines or showing both the filtered and unfiltered in 2 columns and getting your figures from there.