遍历行，隐藏不包含数组中列出的值的行

Question

我想隐藏不包含特定值的行。

我定义了我在数组中寻找的值，然后在整个for循环中迭代索引值。我还以类似的方式遍历复选框。

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

Answer 1

隐藏行

• 不是测试。

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