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

 

[Ken Cowen]

There are lots of ways to do that. How do you want to identify the search string? What kind of functionality do you need to restore the data? Will it always be the same 150x6 cells?

Assuming your search string is the letter "a", as hard coded below, the following code will hide all the rows, then unhide any that have an "a" in columns 1 through 6.

Sub test()


Dim i As Integer, j As Integer
Dim str As String

str = "a"

Cells(1, 1).Resize(150, 1).EntireRow.Hidden = True

For i = 1 To 150
For j = 1 To 6
If Application.Substitute(str, Cells(i, j), "") <> str Then
Cells(i, j).EntireRow.Hidden = False
End If
Next j
Next i


End Sub


This should get you started. Good luck.

Ken

 

[ajc623]

Hi Ken, I would like to have the search string be editable so it could vary based on what you were searching for. Ideally you could enter a search term in A1, row 2 would be blank and row 3 would have headers. The data would start in row 4 and would go down to row 150 for now (this number will greatly increase). if there was a way to enter the search term and have those rows only containing the data show and then a way to reverse it to show all the rows again that would be ideal as I could just have two buttons with one for search and the other for "show all" of something similar. The sheet I am working on contains information related to various materials we work with at work such as, mfg, product name, product number etc. and we are trying to simplify a way to search through the entire list.

 

[vcoolio]

Hello Andrew,

Based on what you have said above and what Ken has started to do, perhaps the following code is near to what you would like to do:-

Sub FindIt()

Application.ScreenUpdating = False

Dim lRow As Long
Dim Srch As String

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)
If cell.Value = Srch Then
cell.EntireRow.Hidden = False
End If
Next

Application.ScreenUpdating = True
Range("A1") = "Search"

End Sub

Following is a link to my test work book for you to play with:-

https://www.dropbox.com/s/t5u2cidmkkcdxqi/Ajc623.xlsm?dl=0

In the sample, I've used "Cat", "Dog", "Mouse" and "Canary" as the criteria to search for. Enter one of these in the search box in cell A1 and then click on "GO". All the other data should be hidden. Basically, the code will allow you to select any criteria in your actual data set. To unhide it, click on the "Show All" button.

The unhide code is in module 2 and is as follows:-

Sub Unhide()
ActiveSheet.Columns("A:G").EntireRow.Hidden = False
End Sub

I hope that this helps.

Cheerio,
vcoolio.

BTW, the criteria entered into the search box needs to be case sensitive.

 

[Ken Cowen]

It looks like vcoolio covered everything you wanted, this time.
If you prefer not to have a case sensitive search, change

If cell.Value = Srch Then
<strike></strike>
to

If ucase(cell.Value) = ucase(Srch) Then
<strike></strike>

Good luck
Ken

 

[ajc623]

Hi Ken and vcoolio, thanks for the work that is almost exactly what I am looking for. The only change would be is it possible to have the search find cells that contain the search string as part of the cell and not as the only data in the cell. So on vcoolio's sheet he uploaded if you searched for "can" it would would return rows with "canary" or if you searched for "12" it would return rows that contained "data 12"?

Andrew

 

[vcoolio]

Hello Andrew,

You could change this line:-

If cell.Value = Srch Then

to

If cell.Value Like Srch & "*" Then

You should then be able to type in "Can" or "Mou" to return similar values.

Cheerio,
vcoolio.

 

[ajc623]

I tried that out and it is close but it will only show the row if the cell begins with with the search term and not if the row contains the search term. For instance if I search for "Can" it will return Canary but would not return "Big Canary" if that was in a cell. Also is there a way to add back the case insensitive search? Thanks again for the help

Andrew

 

[ajc623]

I was wondering if anyone might be able to assist with what I am trying to do. I was able to get vcoolio and Ken to help me get most the way but have one item I would still like the sheet to be able to do. On the link below you can see the current version which allows the user to enter a search term in A1 and if that term is in a cell below it will show just those rows. The item that is missing is I need it to return the search term if the row contains the term in any portion of a cell. For example if I searched for "cat" I would want it to return any row that had, cat, duplicate or copycat.

Here is the link with the current code below that

https://www.dropbox.com/s/t5u2cidmkkcdxqi/Ajc623.xlsm?dl=0

Sub FindIt()

Application.ScreenUpdating = False


Dim lRow As Long
Dim Srch As String


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)
If UCase(cell.Value) = UCase(Srch) Then  
cell.EntireRow.Hidden = False
End If
Next


Application.ScreenUpdating = True
Range("A1") = "Search"


End Sub

Thanks Andrew

 

[Ken Cowen]

I tried that out and it is close but it will only show the row if the cell begins with with the search term and not if the row contains the search term. For instance if I search for "Can" it will return Canary but would not return "Big Canary" if that was in a cell. Also is there a way to add back the case insensitive search? Thanks again for the help

Andrew

It is a little messy as there are apparently 3 options we need to cover, depending on whether the search string is at the beginning, the end, or the entire cell.

Try something like

Sub FindIt()


Dim lRow As Long
Dim Srch As String
Dim test1 As Boolean, test2 As Boolean, test3 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 = cell.Value = Srch
test2 = cell.Value Like Srch & "*"
test3 = cell.Value Like "*" & Srch

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


Next


End Sub

Good luck.
Ken