mardi 1 septembre 2015

VBA: Unhide rows which contains value in an Array from a checkbox

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