Hide a row if contains specific text

[ukphoenix]

Hi Guys and Girls

This is probably a real easy fix but its got me scratching my head. Have searched the net with no luck.
I have a macro that need to hide a row based on a specific word appearing within the column

So the column in question is column D as per my range in the code (D3:D8000) it needs to look through this column for the word 'shop' and then hide the row if found. The issue is the word shop is not the only word in the D cell (ie Shop Alpha, Shop Bravo) so I need it to recognise the word Shop in the phrase and hide that row.

The code also needs to look for a 2nd word and hide that row aswell (ie Board)
So in this picture example it would hide all rows except those containing Paper

TIA for any help

Sub HideRows()

    Dim rCheck As Range
    Dim rHide As Range
    Dim rCheckCell As Range

    Set rCheck = Workbooks("WS TemplateV2.xlsm").Worksheets("3.Shipment details").Range("D3:D8000")
    rCheck.EntireRow.Hidden = False

    For Each rCheckCell In rCheck.Cells
        If InStr(1, rCheckCell, "Shop", vbTextCompare) > 0 Then
            If Not rHide Is Nothing Then Set rHide = Union(rHide, rCheckCell) Else Set rHide = rCheckCell
        End If
    Next rCheckCell




    If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True

End Sub

 

[Yongle]

Try this
Note the simple trick to avoid using If Not rHide Is Nothing Then Set rHide = Union(rHide, rCheckCell) Else Set rHide = rCheckCell

Sub HideRows()
    Dim rCheck As Range
    Dim rHide As Range
    Dim rCheckCell As Range
    Set rCheck = Workbooks("WS TemplateV2.xlsm").Worksheets("3.Shipment details").Range("D3:D8000")
       
    rCheck.EntireRow.Hidden = False
   Set rHide = rCheck.Offset(rCheck.Rows.Count).Resize(1)
   
    For Each rCheckCell In rCheck.Cells
        If InStr(1, rCheckCell, "Shop", vbTextCompare) > 0 Then Set rHide = Union(rHide, rCheckCell)
        If InStr(1, rCheckCell, "Board", vbTextCompare) > 0 Then Set rHide = Union(rHide, rCheckCell)
    Next rCheckCell
    If Not rHide Is Nothing Then rHide.EntireRow.Hidden = True
   
End Sub

 

[Yongle]

Beware
I made a late edit

Set rHide = rCheck.Offset(rCheck.Rows.Count).Resize(1)

 

[ukphoenix]

Works a treat thanks and can now see if I need to add anymore search words what I need to add.

On a side note the code hides the row. What would i need to do to it if I wanted it to delete the row?

 

[Yongle]

On a side note the code hides the row. What would i need to do to it if I wanted it to delete the row?

amend the last line to

If Not rHide Is Nothing Then rHide.EntireRow.Delete

 

[Yongle]

Works a treat thanks and can now see if I need to add anymore search words what I need to add.

If there are many words, avoid code repitition like this

Dim x As Variant
    For Each rCheckCell In rCheck.Cells
        For Each x In Array("Shop", "Board","Apple","Orange")
            If InStr(1, rCheckCell, CStr(x), vbTextCompare) > 0 Then Set rHide = Union(rHide, rCheckCell)
        Next x
    Next rCheckCell