Filter Excel column for cells that contain various combinations of characters

Question

I am trying to filter a range in Excel, say DATA, based on if cells in another column, say CHECKBOXES are checked via a checkbox. It would look something like this:

DATA
aa
ab
ac
ba
bb
bc
ca
cb
cc

CHECKBOXES
a [x]
b [x]
c [x]

So, if 'a' were checked under the CHECKBOXES column, the DATA column would show cells that only contained the letter a, not 'b' or 'c'. But if 'a' and 'b' were checked, the DATA column would show cells that contained either the letters a OR b, but not 'c'. If all three 'a', 'b', and 'c' were checked, there would obviously not be any filtering needed.

I would like to implement this across a large range of data with both columns. Any and all help is much appreciated, Thank You.

Answer 1

You cannot do this the way you've described.

1. Filtering requires a columns header. Data would have to start in row 2, not row 1.
2. Filtering or hiding can only be performed on full rows or full columns. If the A's were hidden, your checkboxes in C1:C3 would also be hidden.

Rethink your proposal and try to accomplish something towards a solution. If you run into trouble, edit your post to include new details.

if 'a' were checked under the CHECKBOXES column, the DATA column would show cells that only contained the letter a, not 'b' or 'c'. But if 'a' and 'b' were checked, the DATA column would show cells that contained either the letters a OR b, but not 'c'. If all three 'a', 'b', and 'c' were checked, there would obviously not be any filtering needed.

Here's a method that works.

• Use Developer, Controls, Insert to add three ActiveX Checkboxes. Do not add Form Control Checkboxes and do not add Option buttons of any kind.
• One by one, right-click the ActiveX Checkboxes and access the Properties. Change the Names to CheckBoxA, CheckBoxB and CheckBoxC and the Captions to A, B and C.
• Resize the checkboxes as appropriate and position them in the top row where they will not be hidden by filtering values. When you're happy with the results, click Developer, Controls, Design Mode to exit Design Mode.

• Right-click the worksheet's name tab and choose View Code. Paste in the following.

  Option Explicit
  
  Sub CheckBoxABC_Click()
  
      Dim i As Long, dict As Object, str As String
  
      Set dict = CreateObject("scripting.dictionary")
      dict.comparemode = vbTextCompare
  
      If AutoFilterMode Then AutoFilterMode = False
  
      With Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
  
          For i = 2 To .Rows.Count
              'store the cell value
              str = .Cells(i, "A").Value
  
              'see if it might belong and add it
              If CBool(InStr(1, str, "A", vbTextCompare)) And .Parent.CheckBoxA Then _
                  dict.Item(str) = vbNullString
              If CBool(InStr(1, str, "B", vbTextCompare)) And .Parent.CheckBoxB Then _
                  dict.Item(str) = vbNullString
              If CBool(InStr(1, str, "C", vbTextCompare)) And .Parent.CheckBoxC Then _
                  dict.Item(str) = vbNullString
  
              'see if it doesn't belong and remove it
              If dict.Exists(str) And CBool(InStr(1, str, "A", vbTextCompare)) And Not .Parent.CheckBoxA Then _
                  dict.Remove str
              If dict.Exists(str) And CBool(InStr(1, str, "B", vbTextCompare)) And Not .Parent.CheckBoxB Then _
                  dict.Remove str
              If dict.Exists(str) And CBool(InStr(1, str, "C", vbTextCompare)) And Not .Parent.CheckBoxC Then _
                  dict.Remove str
  
          Next i
  
          If dict.Count > 0 Then
              .AutoFilter field:=1, Criteria1:=dict.keys, Operator:=xlFilterValues, VisibleDropDown:=False
          Else
              .AutoFilter field:=1, Criteria1:="Z", VisibleDropDown:=False
          End If
  
      End With
  End Sub
  
  Private Sub CheckBoxA_Click()
      CheckBoxABC_Click
  End Sub
  
  Private Sub CheckBoxB_Click()
      CheckBoxABC_Click
  End Sub
  
  Private Sub CheckBoxC_Click()
      CheckBoxABC_Click
  End Sub
  

• Your results should be similar to the following.

Answer 2

Well this can be done a few different ways. However, this is one way you can acomplish what you are trying to do. Create three checkboxes by using design mode from the developer tab and then simply assign 3 macros to do the 3 different buttons. You can also just create one sub and than just have that one sub(macro) do all the work. Is really up to you.

 Sub OptionButton1_Click()

 Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$10").AutoFilter Field:=1, Criteria1:="=a*", _
        Operator:=xlAnd

End Sub

Sub OptionButton2_Click()
 Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$10").AutoFilter Field:=1, Criteria1:="=b*", _
        Operator:=xlAnd
End Sub

Sub option3_click()
 Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$10").AutoFilter Field:=1, Criteria1:="=c*", _
        Operator:=xlAnd
End Sub