Hide rows not containing a word

[Keith_C]

Hello, this is going to be a slightly long question. I will try to be as detailed as possible.

I have a workbook with data, and column F contains a long string of text, which is essentially a description. Column E contains either one of two identifiers, "Main" or "Related". When a "Related" lies directly beneath a "Main", the two are considered to be bound as a group.

1. A single "Main" can have any number of "Related" rows bound to it. This is a group.
2. A "Main" can exist as a standalone entity. This is also considered a group.
3. A "Related" cannot exist without being bound to a "Main".

I would like to be able to hide all groups that do not have a particular word "WORD1" in the description (Column F) of the "Main" that is associated with the group. Using the below table as an example, if only Description4 contains my chosen word "WORD1", then I would want to hide rows 1-3, 8-10.

Preferably this word can be entered into a cell, and I can repeat the process with other words (WORD2, WORD3 etc.).

Any help would be immensely appreciated.

	A	B	C	D	E	F
1					Main	Description1
2					Main	Description2
3					Related	Description3
4					Main	Description4
5					Related	Description5
6					Related	Description6
7					Related	Description7
8					Main	Description8
9					Main	Description9
10					Main	Description10

<tbody>
</tbody>

 

[JLGWhiz]

Let's do it using cell G1 as the criteria cell

ub t()
Dim crt As String, i As Long
With ActiveSheet
    crt = .Range("G1").Value
    For i = 2 To .Cells(Rows.Count, "F").End(xlUp).Row
        If InStr(.Cells(i, 6).Value, crt) > 0 Then
            x = i + 1
            Do While LCase(.Cells(x, 5).Value) <> "main"
                Rows(x).Hidden = True
                x = x + 1
            Loop
            .Rows(i).Hidden = True
        End If
    Next
End With
End Sub

 

[DanteAmor]

To be automatic and every time you change the data in cell G1. Put the following code in the events of your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("G1")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        
        Application.ScreenUpdating = False
        If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
        lr = Range("F" & Rows.Count).End(xlUp).Row
        With Range("G2:G" & lr)
            .Formula = "=IF(RC[-2]=""Main"",IF(IFERROR(SEARCH(R1C7,RC[-1]),0)>0,1,0),IF(R[-1]C=1,1,0))"
            .Value = .Value
        End With
        ActiveSheet.Range("A1:G" & lr).AutoFilter Field:=7, Criteria1:="=1"
        Range("G2:G" & lr).ClearContents
    End If


End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

 

[Keith_C]

Hi Whiz, Dante,

Thank you both very much for taking the time to help me. I ended up using Dante's code which worked brilliantly.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.