VBA: optimizing userform checkbox values for filtering

Question

I created a macro that filters and formats an Excel file with a few thousand lines based on a fixed list of 10 errors (all contained in the same column), and it works just fine. I then decided to try to take it to the next level by giving the option to select which errors should be filtered. It works, but it is kind of slow because I suppose I did not optimize it at all. I created a UserForm with 10 checkboxes and 10 connected public string values check1-check10 in the form of

Public Sub CheckBox1_Click()
If CheckBox1 Then
check1 = "ERROR1"
End If
End Sub

I then use these values to filter like this

ActiveSheet.Range("BE1").AutoFilter Field:=57, _
Criteria1:=Array(check1, check2, check3, check4, check5, check6, check7, check8, check9, check10), _
Operator:=xlFilterValues

It works, but it is extremely slow. I am suspecting because if one or more of these values is empty, it still filters for empty values? Is this really the problem? Or is this rather ok and another part of my following code could be making it slow? Any idea how I can improve my macro to make it run faster?

EDIT: adding the whole code as it does not fit into a comment

Sub form()

UserForm1.Show

    MsgBox ("       Select the dump to format"), , "YAY! Fails!"
    MySchedule = Application.GetOpenFilename
    If MySchedule = False Then
    MsgBox ("       Y U DO DIS?"), , "Y???"
    Exit Sub
    End If
    Workbooks.Open (MySchedule)
    
    Worksheets(1).Activate
    
    ActiveSheet.Range("BE1").AutoFilter Field:=57, _
    Criteria1:=Array(check1, check2, check3, check4, check5, check6, check7, check8, check9, check10), _
    Operator:=xlFilterValues
        
    Cells.Select
    Selection.Sort , Key1:=Range("BE1"), Order1:=xlAscending, Header:=xlYes
    
    Range("N2").Select
    Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select

    Selection.Copy
    
    Sheets.Add After:=Worksheets(1)
    Sheets.Add After:=Worksheets(2)
    
    Worksheets(2).Activate
    
    Range("A1").Select
    ActiveSheet.Paste
    
    Dim LR As Long, ws As Worksheet, Criteria As Variant
    Set ws = ActiveSheet
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 1 To LR
    Range("B" & i).Value = i
    Next i
    
    Criteria = Worksheets(2).Range("A1:A" & LR)
    
    Worksheets(1).Activate
    
    Cells.AutoFilter
    
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    ActiveSheet.Range("A1:DB" & LR).AutoFilter Field:=14, Criteria1:=Application.Transpose(Criteria), Operator:=xlFilterValues
    
    Set ws = ActiveSheet
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws.Range("A1:DB" & LR).SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Worksheets(3).Activate
    Range("A1").Select
    ActiveSheet.Paste
    
    Columns("A:O").Hidden = True
    Columns("Q:S").Hidden = True
    Columns("W:AB").Hidden = True
    Columns("AD:AN").Hidden = True
    Columns("AQ:AS").Hidden = True
    Columns("AY:BC").Hidden = True
    Columns("BG:BH").Hidden = True
    Columns("BM:BS").Hidden = True
    Columns("BV:BW").Hidden = True
    Columns("BY:BZ").Hidden = True
    Columns("CB:DB").Hidden = True
    
    Worksheets(2).Activate
    Set ws = ActiveSheet
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    Set myrange = Range("A1:B" & LR)
    
    Worksheets(3).Activate
    Set ws = ActiveSheet
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = 2 To LR
    Set sequenceno = Range("N" & i)
    Range("DE" & i).Value = Application.WorksheetFunction.VLookup(sequenceno, myrange, 2, False)
    Next i
    
    Cells.Select
    Selection.Sort , Key1:=Range("DE1"), Order1:=xlAscending, Header:=xlYes, Key2:=Range("P1"), Order1:=xlAscending, Header:=xlYes
    
    Columns("DE").Delete
    
    Application.DisplayAlerts = False
    Worksheets(2).Delete
    Worksheets(1).Delete
    Application.DisplayAlerts = True
    
    Worksheets(1).Activate
    
    Columns("P").ColumnWidth = 2
    
    Set ws = ActiveSheet
    
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LR To 2 Step -1
    If Range("P" & i) <> "" And Range("BD" & i) = "" Then Rows(i).Delete
    Next
    
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = LR To 3 Step -1
    If Range("N" & i).Value <> Range("N" & i - 1).Value Then Rows(i).Insert
    Next i
    
    LR = ws.Range("A" & Rows.Count).End(xlUp).Row
    If WorksheetFunction.CountA(ws.Range("BT2:BT" & LR).SpecialCells(xlCellTypeVisible)) = 0 Then Columns("BA").Hidden = True
    If WorksheetFunction.CountA(ws.Range("BU2:BU" & LR).SpecialCells(xlCellTypeVisible)) = 0 Then Columns("BB").Hidden = True
    If WorksheetFunction.CountA(ws.Range("BX2:BX" & LR).SpecialCells(xlCellTypeVisible)) = 0 Then Columns("BE").Hidden = True
    If WorksheetFunction.CountA(ws.Range("CA2:CA" & LR).SpecialCells(xlCellTypeVisible)) = 0 Then Columns("BM").Hidden = True
    
    Dim filename As String, filename2 As String, file_name As String
    filename = ActiveWorkbook.Name

    If Len(GetNumeric(filename)) = 3 Then
    filename2 = Right(GetNumeric(filename), 2)
    Else
    filename2 = ("0" & Right(GetNumeric(filename), 1))
    End If
    
    ActiveSheet.Name = ("all_fails_w" & filename2)
    
    file_name = ("all_fails_w" & filename2)
    ActiveWorkbook.SaveAs filename:=file_name
    ActiveWorkbook.Close
    
End Sub

Siddharth Rout · Accepted Answer · 2025-09-10 07:42:28Z

Passing a fixed 10-element array that includes empty strings does make Excel try to match blanks, which can slow things a bit and also changes the result if the column has blanks.

The bigger slowdown I usually see, though, comes from

Reformatting row-by-row after each filter step (which may not be in your case), and
Leaving calc/screen updating/events on.

Try this (Untested). I have commented the code. If you still have quesitons or face any issues/bugs, simply ask.

Option Explicit

'~~> Call this from your form after the user clicks Apply/OK
Private Sub ApplyErrorFilter(wsTarget As Worksheet, ErrField As Long)
    Dim t As Double: t = Timer

    '~~> Speed switches
    Dim calcMode As XlCalculation
    
    With Application
        calcMode = .Calculation
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayStatusBar = False
        .Calculation = xlCalculationManual
    End With

    On Error GoTo Whoa

    '~~> Build a dynamic criteria array from checked boxes only
    Dim Crit() As Variant
    Crit = SelectedErrorsFromCheckBoxes(Me)  '<~~ Returns a 1-D Variant array

    With wsTarget
        '~~> Ensure there is an AutoFilter header row
        .AutoFilterMode = False


        '~~> Apply or clear filter based on selections
        If IsArray(Crit) Then
            .Range("A1").AutoFilter Field:=ErrField, _
                                    Criteria1:=Crit, _
                                    Operator:=xlFilterValues
        End If
    End With

    '~~> Do any formatting here in vectorized operations, not row-by-row
    ' targetSheet.Range("B:B").NumberFormat = "0.00"         '<~~ example
    ' targetSheet.UsedRange.Columns.AutoFit                  '<~~ example
    
LetsContinue:
    '~~> Restore app state
    With Application
        .Calculation = calcMode
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayStatusBar = True
    End With
    
    Debug.Print "Filter done in", Format(Timer - t, "0.000"), "sec"
    Exit Sub

Whoa:
    '~~> Always restore app state even on errors
    Resume LetsContinue
End Sub

'~~> Collect checkbox captions/tags from the form into a 1-D array
Private Function SelectedErrorsFromCheckBoxes(frm As Object) As Variant
    Dim tmp As Collection: Set tmp = New Collection
    Dim Ctl As Control

    For Each Ctl In frm.Controls
        If TypeName(Ctl) = "CheckBox" Then
            If Ctl.Value Then
                '~~> Use c.Tag if you prefer a stable code like "ERROR1"
                If Len(Ctl.Caption) > 0 Then tmp.Add Ctl.Caption
            End If
        End If
    Next Ctl

    If tmp.Count = 0 Then
        '~~> Return Nothing to mean "no filter"
        Exit Function
    End If

    '~~> Convert Collection to a true 1-D variant array
    Dim arr() As Variant, i As Long
    ReDim arr(1 To tmp.Count)
    For i = 1 To tmp.Count
        arr(i) = tmp(i)
    Next i
    SelectedErrorsFromCheckBoxes = arr
End Function

this is way above my paygrade. I added the whole code to my original post, maybe this way it will be easier to determine why it is so slow. I am filtering by these 10 values but then I need to also keep adjacent lines related to each filtered line, so I am copying values from column N to a new sheet (value which filtered and related lines have in common), and then I use a helper column to keep all lines that have a N value from the list on the other sheet). the rest is just formatting. filtering by static values instead of check1-check10 it is way faster

Collectives™ on Stack Overflow

VBA: optimizing userform checkbox values for filtering

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related