Text Filter based on Changing Cell Macro

[johnfbkennedyy]

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:

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!

 

[LazyBug]

Hello! I didn't get your goal, but maybe (code to a sheet module):

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]

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]

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]

How about

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]

W

How about

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

 

[Fluff]

Glad to help & thanks for the feedback.