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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
594
Office Version
  1. 2010
Platform
  1. Windows
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?

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")
                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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
568
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,517
Messages
5,636,804
Members
416,941
Latest member
shazzaxyz

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
Top