searching multiple columns in a worksheet

[ajc623]

So I have a worksheet with data in 6 columns A-G and down to row 150 and I am wanting to be able to do a search to find specific text in any one of the columns. I like the functionality of the auto filter search but you can only do that in one column and I am wondering if there is a way to search the entire range and have only the rows that contain the search term to show up similar to how auto filer search works in each column? Thanks

Andrew

 

[ajc623]

Hi Ken, Thanks so much for the help. I tweaked it a bit to allow for the case insensitive as well as if the search term was in the middle of a word but it works great!

Sub FindIt()



Dim lRow As Long
Dim Srch As String
Dim test1 As Boolean, test2 As Boolean, test3 As Boolean, test4 As Boolean




lRow = Range("A" & Rows.Count).End(xlUp).Row
Srch = Range("A1").Value




Range("A4:A" & lRow).EntireRow.Hidden = True




For Each cell In Range("A4:G" & lRow)




test1 = UCase(cell.Value) = UCase(Srch)
test2 = UCase(cell.Value) Like UCase(Srch) & "*"
test3 = UCase(cell.Value) Like "*" & UCase(Srch)
test4 = UCase(cell.Value) Like "*" & UCase(Srch) & "*"


If Application.Or(test1, test2, test3, test4) Then
cell.EntireRow.Hidden = False
End If




Next




End Sub

 

[vcoolio]

Hello Andrew,

Another method would be to use a Worksheet_Change event with autofilter, as follows:-

Private Sub worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then
          Me.AutoFilterMode = False
          Me.Rows(3).AutoFilter 8, True
    End If
    
End Sub

"

although with this method you'll need a helper column with the following formula copied down as far as you need (for the sake of the exercise, I've used Column H and hidden it.) :

=ISNUMBER(MATCH("*" & $A$1 & "*", A4:G4, 0))

The formula determines if the returned value is true (matches the search box) for each row. Hence the code filters on "True".

This method is case insensitive and will also allow you to just use a letter or two (say "c" for cat or "mo" for mouse. "Ca" should return "cat" or "canary etc.).

Just overwrite whatever is in the search box for a new search then click away for the results to come up.

To implement the code, right click on the sheet tab and then select "view code". In the big white field that appears, paste the above code.

Following is a link to the test work book showing how it works:-

https://www.dropbox.com/s/99a4zmnz47gqhou/Ajc623 (2).xlsm?dl=0

Just another option for you.

Cheerio,
vcoolio.