Search code examples
excelvbaexcel-tables

Implementing a 'tag'-like filter for a column in an Excel table?


I have a column in an excel table that may contain values such as:

  • 5
  • 7,1,8
  • 3,5
  • ALL

I want to implement a macro that can filter for these values, such that if I filter for "5", the first and third results come up. Also, I included the ALL in the list because there may be text tags added in the future. Here's what I have so far, but it gets inconsistent results:

Sub ContainsFilter()
    Dim strName As String
    strName = InputBox("What would you like to search for?")
    If strName = "" Then
    ActiveSheet.ShowAllData
    Else
    ActiveSheet.ListObjects("Table").Range.AutoFilter Field:=7, Criteria1:="=*" & strName & "*", Operator:=xlAnd
    End If
End Sub

Solution

  • The following should work:

    Option Explicit
    
    Public Sub ContainsFilter()
        Dim InputReturn As Variant
        InputReturn = Application.InputBox(Prompt:="What would you like to search for?", Title:="Filter Field 7", Type:=2) ' type 2 = string
        
        If VarType(InputReturn) = vbBoolean And InputReturn = False Then
            ' User pressed cancel button or X in the right corner
            Exit Sub
        End If
        
        If InputReturn = vbNullString Then
            ActiveSheet.ShowAllData
        Else
            ActiveSheet.ListObjects("Table").Range.AutoFilter Field:=7, Criteria1:=Array("*" & InputReturn & "*", InputReturn), Operator:=xlFilterValues
        End If
    End Sub