I have three checkboxes (Cat, Dog, Mouse) and I have the below data set in excel (Letters are columns).
A B C
Cat Cat Mouse
Dog Mouse Cat
Mouse Mouse Dog
Dog Cat Cat
and I have the following code:
Dim num As Integer
arrcriteria = Array("Cat", "Dog", "Mouse")
Set temprange = ActiveSheet.Range("A1").End(xlDown) Set temprange = Range(Range("A2"), temprange)
For Each z In temprange
z.EntireRow.Hidden = True
For num = 1 To 3
If Me.Controls("Checkbox" & num) Then
If InStr(1, z.Value, arrcriteria(num - 1), 1) = 1 Then
z.EntireRow.Hidden = False
Elseif InStr(1, z.offset(0,1).Value, arrcriteria(num - 1), 1) = 1 Then
z.EntireRow.Hidden = False
Elseif InStr(1, z.offset(0,2).Value, arrcriteria(num - 1), 1) = 1 Then
z.EntireRow.Hidden = False
Else
'Remain hidden
End If
End if
Next num
However I want to change the code so that it hides rows when only all selected criteria are present in that row. For example if I select 'Cat' and 'Dog' the below is shown only:
A B C
Dog Mouse Cat
Dog Cat Cat
Do you know how it can be done?
Thank you in advance
Will
Aucun commentaire:
Enregistrer un commentaire