PDA

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



jessmall
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, _
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

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

jessmall
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

jessmall
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.

i.e.

Hello
Hellogoodbye
goodbye

Searching for "goodbye" will only return row 2

Bob Phillips
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.

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

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