I’m creating a filter in VBA that will scan a large number of rows of data and hide rows that don’t contain certain values. I define the values that I'm looking for in an array and then just iterate an index value throughout the for loop. I also iterate through the checkboxes similarly. I'm just a little stumped as to why it’s not working. Below is the code:

Private Sub FilterResults_Click()
    'checkbox count variables
    CB_Start = 2
    CB_End = 15
    
    'row count variables
    StartRow = 2
    EndRow = 2999
    ColNum = 7
    
    'Array
    Dim SubProduct() As Variant
    SubProduct = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N")
    
    'product count variable
    k = 0
    
    'loop through checkboxes
    For i = CB_Start To CB_End
        If UserForm1.Controls("CheckBox" & i).Value = True Then
            'loop through rows of data
            For j = StartRow To EndRow
                'check if cells contains array index of SubProduct
                If InStr(Cells(j, ColNum).Value, SubProduct(k)) = 0 Then
                    Cells(j, ColNum).EntireRow.Hidden = True
                Else '<> 0
                    Cells(j, ColNum).EntireRow.Hidden = False
                End If
            Next j
        Else 'Checkbox not true
            For j = StartRow To EndRow
                'check if cells do not contain array index of SubProduct
                If InStr(Cells(j, ColNum).Value, SubProduct(k)) <> 0 Then
                    Cells(j, ColNum).EntireRow.Hidden = False
                Else '= 0
                    Cells(j, ColNum).EntireRow.Hidden = False
                End If
            Next j
        End If
    
        'increment
        k = k   1
    Next i
    
    'hide userform on filter
    UserForm1.Hide
End Sub

Would appreciate any help. Thanks so much!

CodePudding user response：

Hide Rows

• Not tested.

Option Explicit

Private Sub FilterResults_Click()
    
    ' Crucial info: 14 checkboxes associated with 14 elements in 'SubProduct'.
    
    'checkbox count variables
    Const cbStart As Long = 2
    Const cbEnd As Long = 15
    
    'row count variables
    Const StartRow As Long = 2
    Const EndRow As Long = 2999
    Const ColNum As Long = 7
    
    'Array
    Dim SubProduct() As Variant
    SubProduct = VBA.Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N")
    ' 'VBA' is ensuring a zero-based array ('Option Base' related).

    'product count variable
    Dim k As Long
    
    'range
    Dim rg As Range
    Set rg = Cells(StartRow, ColNum).Resize(EndRow - StartRow   1)
    ' Hide all rows. Then, in the loop, only unhide the matching ones.
    rg.EntireRow.Hidden = True
    
    Dim cCell As Range
    Dim cProduct As String
    Dim i As Long
    
    'loop through checkboxes
    For i = cbStart To cbEnd
        If UserForm1.Controls("CheckBox" & i).Value = True Then
            'write the current product to a variable
            cProduct = SubProduct(k)
            'loop through rows of data
            For Each cCell In rg.Cells
                'check if current cell contains current product
                ' 'CStr' will prevent failure if error value
                ' 'vbTextCompare' will ignore case 'A = a'
                If InStr(1, CStr(cCell.Value), cProduct, vbTextCompare) > 0 Then
                    cCell.EntireRow.Hidden = False
                End If
            Next cCell
        End If
        'increment product index
        k = k   1
    Next i
    
    'hide userform on filter
    UserForm1.Hide

End Sub