Table filter for a specific word in a cell

MrVBAConfused

New Member
Joined
Jul 29, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Table filter question.JPG
    Table filter question.JPG
    127.1 KB · Views: 17

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Max1616

Active Member
Joined
Nov 25, 2015
Messages
401
Perhaps try something like this for hiding rows that do not contain your text value:
VBA Code:
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
 
Last edited by a moderator:

MrVBAConfused

New Member
Joined
Jul 29, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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! :oops:
 

Max1616

Active Member
Joined
Nov 25, 2015
Messages
401
Hmm, interesting. It was working for mutliple words in my test workbook. What is the current location/range of your table?
 

MrVBAConfused

New Member
Joined
Jul 29, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Nov 25, 2015
Messages
401
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:\
VBA Code:
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

New Member
Joined
Jul 29, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:\
VBA Code:
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

New Member
Joined
Jul 29, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 29, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,147,731
Messages
5,742,852
Members
423,758
Latest member
accountfornada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top