Hide Rows based on 2 specific cell values

[Nick70]

Hi,

I have a table in a range (A2:D100) with fruit names.

I would like a macro that hides all rows that have value = "pear" in column A and at the same time value "apple" in column C (in same row).

If cell in column A has "pear" (e.g. in cell A4) but value in cell C4 does not have "apple" then the row is NOT hidden.
For row to be hidden cell A4 must have "pear" and cell C4 must have "apple" so both conditions must be met.

How do I do that?

Thanks,
N.

 

[kevin9999]

One possibility...

Option Explicit
Sub HideFruit()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Dim rng As Range, r As Range, i As Long
    Set rng = ws.Range("A2:D100")
    For i = 1 To 99
        If rng.Cells(i, 1) = "pear" And rng.Cells(i, 3) = "apple" Then
            If r Is Nothing Then Set r = rng.Cells(i, 1)
            Set r = Union(r, rng.Cells(i, 1))
        End If
    Next i
    r.EntireRow.Hidden = True
End Sub

 

[Nick70]

Perfect thanks!!

 

[kevin9999]

Perfect thanks!!

Happy to help, and thanks for the feedback

 

[Nick70]

Happy to help, and thanks for the feedback

Hello Kevin,

One last thing please.

If I wanted to add sets of 2 conditions how would the code change.

So for example if cell in column A has "pear" (e.g. in cell A4) but value in cell C4 does not have "apple" then the row is NOT hidden AND
if cell in column A has "cherry" (e.g. in cell A4) but value in cell C4 does not have "apple" then the row is NOT hidden.

 

[Nick70]

Please ignore my latest email as sent by mistake