Text Filter based on Changing Cell Macro

johnfbkennedyy

New Member
Joined
Sep 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi all! I'm new to VBA and I am trying to create a text filter sub based on a changing cell. Basically it serves its function: when I update the search in the cell which is named "search_string" is automatically updates all of column D values that contain that text. The only problem is that when I delete the search, it only shows cells in column D that have contents. So for example if one row in column D has no text, it will not filter it in when the cell of search_string is empty. I have tried to change the text but I don't know how to change the criteria to make it filter the search_string text content including when its blank.

This is the code I have up until now:

VBA Code:
Private Sub Macro2(ByVal Target As Range)
Columns("D:D").Select
    Selection.AutoFilter
    Range("search_string").Select
    Selection.Copy
    ActiveSheet.Range("$D$2:$D$152184").AutoFilter Field:=1, Criteria1:= _
        "=*" & Range("search_string").Value & "*", Operator:=xlAnd
        
End Sub

Thanks in advance!
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
158
Office Version
  1. 2010
Platform
  1. Windows
Hello! I didn't get your goal, but maybe (code to a sheet module):
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet: Set sh = ActiveSheet
    With sh
        If Not Intersect(Target, sh.Range("search_string")) Is Nothing Then
            If IsEmpty(sh.Range("search_string")) Then
                ShowAllData
            Else
                Columns("D:D").AutoFilter
                Range("A1").Copy
                ActiveSheet.Range("$D$2:$D$152184").AutoFilter Field:=1, Criteria1:= _
                    "=*" & Range("search_string").Value & "*", Operator:=xlAnd
            End If
        End If
    End With
End Sub
 

johnfbkennedyy

New Member
Joined
Sep 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
YES! It works! Thank you! It did exactly what I wanted it to do: show all cells even when empty in column D

Regards
 

johnfbkennedyy

New Member
Joined
Sep 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Just one more question, sorry if it is too much to ask. But, when I select the search_string cell to input a value search and accidentally click another cell, I get the error:
Run-time error '1004':
Method 'ShowAllData' of object '_Worksheet' failed

Is there anything that could be editable in the code to fix this? I know you're thinking "well just don't click on other cells when the search_string cell is selected", problem is that the excel will be used by other users which I am afraid that will get scared upon the error and won't know what to do.

Thanks in advance
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("search_string")) Is Nothing Then
      If Range("search_string") = "" Then
         If Me.FilterMode Then Me.ShowAllData
      Else
         Range("A2").AutoFilter 4, "*" & Target.Value & "*"
      End If
   End If
End Sub
 

johnfbkennedyy

New Member
Joined
Sep 22, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
W
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("search_string")) Is Nothing Then
      If Range("search_string") = "" Then
         If Me.FilterMode Then Me.ShowAllData
      Else
         Range("A2").AutoFilter 4, "*" & Target.Value & "*"
      End If
   End If
End Sub
Works great! Thank you so much
 

Watch MrExcel Video

Forum statistics

Threads
1,127,656
Messages
5,626,133
Members
416,164
Latest member
Zhevr

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