Hide rows unless they contain specific values in specific cells

[Nick70]

Hi,

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

I would like a macro that hides all rows in table range which do not satisfy one of these 3 conditions:

1. Cell in column A has value "pear" and equivalent cell in column C has value "apple" (e.g. cell A3 = "pear" and cell C3 ="apple")
or else
2. Cell in column A has value "kiwi" and equivalent cell in column C has value "cherry" (e.g. cell A4 = "kiwi" and cell C4="cherry")
or else
3. Cell in column A has value "melon" and equivalent cell in column C has value "fig" (e.g. cell A5 = "melon" and cell C5 ="fig")

How do I do that?

Thanks,
N.

 

[dalvin647]

Hi Nick70,

I suggest you use a helper column instead of using a macro.

=OR(AND(A2="Pear",C2="Apple"),AND(A2="Kiwi",C2="Cherry"),AND(A2="Melon",C2="Fig"))

I put it in D2, you can just drag down to D100, then filter the helper column to TRUE. Thanks.

 

[DanteAmor]

Hi @Nick70, thanks for posting on the forum.

If you want the macro, try the following:

Sub HideRows()
  Dim duo As String
  Dim c As Range
  
  Application.ScreenUpdating = False
  Cells.EntireRow.Hidden = False
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    duo = c.Value & "|" & c.Offset(, 2).Value
    If Not (duo = "pear|apple" Or duo = "kiwi|cherry" Or duo = "melon|fig") Then
      c.EntireRow.Hidden = True
    End If
  Next
  Application.ScreenUpdating = False
End Sub

 

[Nick70]

Works perfectly thanks!
Macro better because table is actually much larger so cannot use helper column.

 

[Nick70]

One last question please.

How can I amend code so that instead of kiwi in

duo = "kiwi|cherry"

we can have any text including no text (i.e. empty cell)?

I tried

duo = "*|cherry"

but it does not work.

Thanks,
N.

 

[DanteAmor]

duo = "*|cherry"

Try this:

Sub HideRows()
  Dim duo As String
  Dim c As Range
  
  Application.ScreenUpdating = False
  Cells.EntireRow.Hidden = False
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    duo = c.Value & "|" & c.Offset(, 2).Value
    If Not (duo Like "pear|apple" Or duo Like "*|cherry" Or duo Like "melon|fig") Then
      c.EntireRow.Hidden = True
    End If
  Next
  Application.ScreenUpdating = False
End Sub

 

[Nick70]

Great thanks!