Thread: Search on user string and filter column

Firstly thank you to the help I've had recently from here. 1st class site and top notch mods and users.

Now I'm getting a little ambitious.

I have attached a VERY simple version of what I am trying to do.

In esscence, I want the user to be able to type in a string in a search cell and then for the column to show only those cells that contain that text.

So for instance on my attached version, if "green" was typed I want the column to only show green bottles and Green oil.

If "Oil" was typed I want the column to shown Green oil and Blue oil and so on

I'm sure this can be done, and a similar thread is on the first page here but isn't quite what I'm after.

Have you got any pointers?

Thank you

Maybe this in the worksheet module - right-click sheet tab, view code and paste:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim v, i As Long

If Target.Address <> "$B$3" Then Exit Sub

v = Array("10 brown bottles", "purple powder", "blue stuff", "blue oil", "Green bottles", "Green oil")

Range("B4").Resize(UBound(v) + 1).ClearContents

For i = LBound(v) To UBound(v)
    If InStr(1, v(i), Target.Value, 1) > 0 Then
        Range("B" & Rows.Count).End(xlUp)(2) = v(i)
    End If
Next i

End Sub

Another option:

Private Sub cmdSearch_Click()
Dim iFind As String
Dim icell As Long, lastrow As Long

'variables
iFind = Range("B3").Value
lastrow = Range("B" & Rows.Count).End(xlUp).Row

'clears previous search
Range("B4", "B65536").EntireRow.Hidden = False

'code to filter out non search string
For icell = 4 To lastrow
If InStr(1, Range("B" & icell).Value, iFind, vbTextCompare) Then
    'nothing
Else
    Range("B" & icell).EntireRow.Hidden = True
End If
Next icell

End Sub

As ever, problem solved.

Stephen, your code would work but the complete list of products is huge so the code is impracticle for the Array. Sorry if I was unlcear.

stnkynts, that code seems work just fine.

In fact I've combined Stephen's Idea of putting the code in the change method so user doesnt have to click a button to perform the action. It just searches when you the cell loses focus.

Thank you both.

How do I set the thread title to [SOLVED] ?

Ahh one slight problem I just noticed.

Is it possible to filter using part matches. For example if the user was to type "own" in the search box then the row would filter out the "own" of "Brown Bottle"?

Ahh, I seem to have worked that one out on my own, I changed the vbTextCompare to vbBinaryCompare and it works!!

Again thank you