Table filter for a specific word in a cell

[MrVBAConfused]

Hi All,

After doing a bit of digging around the internet, I can't seem to find what I'm after.....

I would like to enter a key word/ phrase etc in a specified cell (in my example it's L4, which would in turn filter the below table ("Table1") to show all rows that contain that word/ phrase

I have found a way to do something similar with a text box linked to the search cell (I've already created in my example and linked to L4), then it Autofilters an additional table (i don't have this) . but their example only works for a column that is specified. (not all of them)

So, in my example, i would enter a word ("help" in this example) then it would filter the table to just show rows 8, 10, 21, 27 and 32. The word could be an exact match or a partial, so i'm thinking a wild card would be needed?

Then to finish off, I would like to click the "clear" button and remove filters. I believe this would work?

Sub CommandButton1_Click()
ActiveSheet.ShowAllData
End Sub

Thank you in advance!
Martin

 

[Max1616]

Perhaps try something like this for hiding rows that do not contain your text value:

Option Compare Text
Private Sub TextBox1_Change()

Dim MatchVal As String
Dim i As Integer
Dim j As Integer
Dim srow As String
Dim lrow As String
Dim lcolumn As String
Dim rowchk As Boolean

Application.ScreenUpdating = False

srow = 8 'Start Row
scolumn = 3 'Start Column
lcolumn = 14 'End Column
lrow = Cells(Rows.Count, scolumn - 1).End(xlUp).Row
MatchVal = TextBox1.Value

If MatchVal = "" Then
    ActiveSheet.Range("1:" & lrow).EntireRow.Hidden = False
    Application.ScreenUpdating = True
    Exit Sub
End If

ActiveSheet.Range("1:" & lrow).EntireRow.Hidden = False

For i = srow To lrow
    For j = scolumn To lcolumn
        If InStr(Cells(i, j), MatchVal) > 0 Then
            rowchk = True
            GoTo Matched
        End If
    Next j

Matched:
    If rowchk = False Then
        Cells(i, j).EntireRow.Hidden = True
        GoTo Nexti
    End If
   
    rowchk = False
Nexti:
Next i

Application.ScreenUpdating = True

End Sub

 

[MrVBAConfused]

Perhaps try something like this for hiding rows that do not contain your text value:
Option Compare Text Private Sub TextBox1_Change() Dim MatchVal As String Dim i As Integer Dim j As Integer Dim srow As String Dim lrow As String Dim lcolumn As String Dim rowchk As Boolean Application.ScreenUpdating = False srow = 8 'Start Row scolumn = 3 'Start Column lcolumn = 14 'End Column lrow = Cells(Rows.Count, scolumn - 1).End(xlUp).Row MatchVal = TextBox1.Value If MatchVal = "" Then ActiveSheet.Range("1:" & lrow).EntireRow.Hidden = False Application.ScreenUpdating = True Exit Sub End If ActiveSheet.Range("1:" & lrow).EntireRow.Hidden = False For i = srow To lrow For j = scolumn To lcolumn If InStr(Cells(i, j), MatchVal) > 0 Then rowchk = True GoTo Matched End If Next j Matched: If rowchk = False Then Cells(i, j).EntireRow.Hidden = True GoTo Nexti End If rowchk = False Nexti: Next i Application.ScreenUpdating = True End Sub

Hi Max,

It worked when i searched for "help". But i entered random words in the table to test it more and if there is similar words it doesn't seem to work?

Also, the word that may be typed could be anything and in any of the columns (not just the ones i added "help" to)

This table will ultimately become a large repository of lessons learned- so they could come to this and search any word they want (say "risk" and it would show all the applicable lessons with the word risk in)

I know that's probably made things harder (sorry)

Regards,
Martin

On a side note, what i thought was the easy bit (the clear function) didn't work either!

 

[Max1616]

Hmm, interesting. It was working for mutliple words in my test workbook. What is the current location/range of your table?

 

[MrVBAConfused]

Hmm, interesting. It was working for mutliple words in my test workbook. What is the current location/range of your table?

Hi Max,

My original sheet has the same range and location as the example screenshot (B8:N33 is "Table1- with the header as B7:N7) the only real difference is my original has different header names and the column widths are set wider (i believe these won't effect the macro) there is some merged cells in my original in cells B2:I5- maybe these are the issue?

Only other option i can think of, is setting up an additional column with a Vlookup that references the search cell (L4) then set up an autofilter to only filter that new column?

Cheers
Martin

 

[Max1616]

Try this. I made some edits to the code that might help. I also added a Sub that you can use to clear the filters:\

Option Compare Text
Private Sub TextBox1_Change()

Dim MatchVal As String
Dim i As Integer
Dim j As Integer
Dim srow As String
Dim lrow As String
Dim lcolumn As String
Dim rowchk As Boolean

Application.ScreenUpdating = False

ActiveSheet.Range("1:" & 100000).EntireRow.Hidden = False

srow = 8 'Start Row
scolumn = 3 'Start Column
lcolumn = 14 'End Column
lrow = Cells(Rows.Count, scolumn - 1).End(xlUp).Row
MatchVal = TextBox1.Value

If MatchVal = "" Then
    Application.ScreenUpdating = True
    Exit Sub
End If


For i = srow To lrow
    For j = scolumn To lcolumn
        If InStr(Cells(i, j), MatchVal) > 0 Then
            rowchk = True
            GoTo Matched
        End If
    Next j

Matched:
    If rowchk = False Then
        Cells(i, j).EntireRow.Hidden = True
        GoTo Nexti
    End If
    
    rowchk = False
Nexti:
Next i

Application.ScreenUpdating = True

End Sub

Sub Clear()

ActiveSheet.Range("1:" & 100000).EntireRow.Hidden = False

End Sub

Its worth noting that this code requires an id to exist in column B for every row in your table.

 

[MrVBAConfused]

Try this. I made some edits to the code that might help. I also added a Sub that you can use to clear the filters:\

Option Compare Text
Private Sub TextBox1_Change()

Dim MatchVal As String
Dim i As Integer
Dim j As Integer
Dim srow As String
Dim lrow As String
Dim lcolumn As String
Dim rowchk As Boolean

Application.ScreenUpdating = False

ActiveSheet.Range("1:" & 100000).EntireRow.Hidden = False

srow = 8 'Start Row
scolumn = 3 'Start Column
lcolumn = 14 'End Column
lrow = Cells(Rows.Count, scolumn - 1).End(xlUp).Row
MatchVal = TextBox1.Value

If MatchVal = "" Then
    Application.ScreenUpdating = True
    Exit Sub
End If


For i = srow To lrow
    For j = scolumn To lcolumn
        If InStr(Cells(i, j), MatchVal) > 0 Then
            rowchk = True
            GoTo Matched
        End If
    Next j

Matched:
    If rowchk = False Then
        Cells(i, j).EntireRow.Hidden = True
        GoTo Nexti
    End If
   
    rowchk = False
Nexti:
Next i

Application.ScreenUpdating = True

End Sub

Sub Clear()

ActiveSheet.Range("1:" & 100000).EntireRow.Hidden = False

End Sub

Its worth noting that this code requires an id to exist in column B for every row in your table.

Hi Max,

this works slightly better, but is still missing some data? (some columns work better than others?) example is if i start typing "tim" it will filter on the word estimating (in column i, but completely miss the word time That appears in column H. various other version of this have the same effect; "pro" picks up "project" in J but not in F, "sc" picks up "time scale" in H but not "schedule" in J etc

very strange i know (it should work!)

but the clear function works perfectly- thank you

 

[MrVBAConfused]

just for additional info-
1) I had all ID column filled in
2) i did a table to the right and entered =ISNUMBER(SEARCH for each column and referenced L4 as the source (to get a true/false) and it picked up every time i changed the word.

Wonder if this could be used in the VBA code somehow?

 

[MrVBAConfused]

just for additional info-
1) I had all ID column filled in
2) i did a table to the right and entered =ISNUMBER(SEARCH for each column and referenced L4 as the source (to get a true/false) and it picked up every time i changed the word.

Wonder if this could be used in the VBA code somehow?

All,

I managed to get it to work very clunky!! (see below) But is there a better way to reference the table (Table1) instead of a start/end row etc?
------------------------------------------------------------------------------------------
Private Sub TextBox1_Change()

'Requires an additional column to do a CountIf " =COUNTIF(B8:N8,"*"&$L$4&"*") " and copied down the entire table (in this example column 15)
Application.ScreenUpdating = False

StartRow = 8
EndRow = 35
ColNum = 15
For i = StartRow To EndRow
If Cells(i, ColNum).Value = "0" Then 'this denotes no match
Cells(i, ColNum).EntireRow.Hidden = True
Else
Cells(i, ColNum).EntireRow.Hidden = False
End If
Next i

Application.ScreenUpdating = True

End Sub
-------------------------------------------------------------------------------------------------

Sub CommandButton1_Click()

Application.ScreenUpdating = False

' unhides all rows and clears contents of TexBox1 (& L4)
ActiveSheet.Range("1:" & 100000).EntireRow.Hidden = False
TextBox1.Text = ""

Application.ScreenUpdating = True

End Sub