View Full Version : Search for all instances of a string then hide all other rows

02-09-2012, 05:42 AM
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:

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, _
' select found data and unhide if row is hidden
If Not rngFoundData Is Nothing Then
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
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

02-09-2012, 05:54 AM
Can you post you workbook? I think we can write much better and faster code.

02-09-2012, 06:16 AM
Actually I was able to write a code using autofilters BUT I can only search one column and I need to search 2 (both colA and colB):

Sub Search()
'written by jessmall
Dim r As Range, filt As Range, j As Long, k As Long

'Set r = Range("A5").CurrentRegion
Dim MyData As String

MyData = InputBox("Search by Format:xyz")
If MyData = "" Then Exit Sub
' search all sheets in workbook

Set r = Range("A5").CurrentRegion

r.AutoFilter field:=1, Criteria1:=MyData & "*"

End Sub

02-09-2012, 06:40 AM
Also I just noticed that this will only search the beginning string in a cell, not anywhere within that cell.



Searching for "goodbye" will only return row 2

02-09-2012, 07:36 AM
Well as you got that far, I will just give you a hint.

Insert a helper column where you add a formula for determining whethere those two criteria are met, and then filter that column.

02-09-2012, 08:01 AM
I don't actually know what a helper column is?

02-09-2012, 08:10 AM
A column to help, one that you create, and is not part of the initial data.