this uses looping instead of filters.
Not perfect but a start.
It looks specifically for 3 keywords. I am tweaking it to look for random amounts of keywords.
It hides rows with unmatched keywords..
I added an unhide script too.
Sub testFilter()
Dim wsSum As Worksheet
Dim lr, r As Long
Dim myKeywords, firstKey, lastKey, mCnt, k
Set wsSum = Sheets("Summary")
wsSum.Activate
With wsSum
lr = .Cells(Rows.Count, "E").End(xlUp).Row
.Cells.AutoFilter
.Range("A8:A200").EntireRow.Hidden = False ' set to max range
If Range("sWord") <> "" Then
myKeywords = Split(Range("sWord"), ",")
firstKey = LBound(myKeywords)
lastKey = UBound(myKeywords)
For mCnt = 0 To lastKey
myKeywords(mCnt) = Trim(myKeywords(mCnt))
Next mCnt
End If
' this only looks for 3 keyword values and errors if not 3 keywords...
For r = lr To 8 Step -1
If .Cells(r, 5).Value <> myKeywords(0) And _
.Cells(r, 5).Value <> myKeywords(1) And _
.Cells(r, 5).Value <> myKeywords(2) Then
.Cells(r, 5).EntireRow.Hidden = True
End If
Next r
End With
End Sub
Sub unHideRows()
Rows("1:200").Select
Range(Selection, Selection.End(xlDown)).EntireRow.Hidden = False
End Sub