Search for all instances of a string then hide all other rows
I am trying to write a code that will allow me to search a string within a cell (xlPart) that is entered into an input box, then continue to find all instances of that string. Then after it finds it, any row that does not have that string contained somewhere in it is hidden.
I can only get my code to find the first instance:
[VBA]
Dim MyData As String
Dim wks As Worksheet
Dim rngFoundData As Range
Dim Rng As Range
Dim HideRNG As Range
Dim Cel As Range
MyData = TextBox1 'User inputs text to be searched
If MyData = "" Then SearchBox.Hide 'If textbox 1 is blank, close and do not run
' search all sheets in workbook
For Each wks In Worksheets
' find data in current worksheet
Set rngFoundData = wks.Cells.Find(what:=MyData, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
' select found data and unhide if row is hidden
If Not rngFoundData Is Nothing Then
wks.Activate
rngFoundData.Select
SearchBox.Hide 'Close search box once string is found
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.EntireRow.Hidden = False 'If the row that the string was located in is hidden, unhide that row
Set Rng = Range("K6:K" & Cells.SpecialCells(xlLastCell).Row) 'If nothing is contained in column K, then hide the row completely
For Each Cel In Rng
If Cel.Value = "" Or Cel.Value = "-" Then
If HideRNG Is Nothing Then
Set HideRNG = Cel
Else
Set HideRNG = Union(HideRNG, Cel)
End If
End If
Next Cel
If Not HideRNG Is Nothing Then
HideRNG.EntireRow.Hidden = True
Set HideRNG = Nothing
End If
Set Rng = Nothing
End If
Exit Sub
End If
Next wks
' tell user data wasn't found
If rngFoundData Is Nothing Then
MsgBox MyData & " was not found.", vbInformation
End If
[/VBA]
