Filter one column with multiple checkboxes on multiple values

**J_Marley** · 11-01-2018, 12:50 PM

Hello all. I am fairly new here so I'll try to make this as coherent as possible. I want to thank jaslake and protonLeah for helping me get started. Also, as a starting point, the thread that my question stemmed from is called "Filter one column with multiple checkboxes" (I am not able to post direct link due to limitations).

My situation is that I have a column with multiple values that I want to filter by "groups". In this instance, column P contains values associated with a location. These locations are associated with a certain channel, but that is not part of the data, and does not exist in a table that I would be able to merge into this table as a separate column. Instead I would like to assign an autofilter to a checkbox for each channel that would filter those grouped values based on the checkbox(s) selected.

I will also admit that VBA is not my strong suit, so many of the terms and language behind this is still foreign to me. The code I have currently created (some of which has been removed for security purposes) is for the autofilter. If there is more info needed or something does not look right, please let me know and I will do my best to fix the issue. Thanks!

**jaslake** · 11-02-2018, 09:09 PM

Hi J_Marley
Gotta admit...not certain what you're looking for...seems to me based on what I see you wish to filter sheet DataFeed, Column P based on Checkboxes in Sheet RunReport…

If Sheet RunReport Checkbox Business is checked then filter Sheet DataFeed, Column P with("001", "0160", "1014", "1019", "1024", "1026", "1136", "1183", "1362", "2016", "2017", "2702", "2703", "3261", "3262", "3267", "3333", "3342", "4013", "4134", "4151", "5012", "5100", "5101", "5109", "6003", "6005", "6017", "6104")

If Sheet RunReport Checkbox Indirect is checked then filter Sheet DataFeed, Column P with
("Z700", "Z705", "Z708", "Z710", "Z711, "Z712", "Z716", "Z718", "Z721", "Z722", "Z724", "Z726", "Z727", "Z728", "Z730", "Z732", "Z734", "Z735", "Z740", "Z741", "Z742")

If Sheet RunReport Checkbox Retail is checked then filter Sheet DataFeed, Column P with
("A020", "A040", "A050", "A090", "A100", "A110", "A130", "A140", "B100", "B200", "B300", "B400", "B500", "B600", "B700", "B800", "B900", "C200", "C300", "C400", "E100", "E200", "F200", "F300", "F400", "F500", "K100", "K200", "K400", "K800", "L200", "N100", "N200", "N300", "P100")

I won't even ask about the "Blanks" in Column P of sheet DataFeed as you didn't address them.

I WILL ask you to show me your desired output as you didn't present that...show me...

**jaslake** · 11-03-2018, 07:42 PM

Hi J_Marley
Looking though Data Feed I see these records...where do they belong??? Are there others??? I need your help Bud...get back to me...in the meantime I'm off...

StartDate EndDate ORDER_STORE_NO
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 BILL
9/1/2018 10/25/2018 BILL
9/1/2018 10/25/2018 BILL
9/1/2018 10/25/2018 BILL
9/1/2018 10/25/2018 RMAS
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 BILL
9/1/2018 10/25/2018 BILL
9/1/2018 10/25/2018 RMAS
9/1/2018 10/25/2018 RMAS
9/1/2018 10/25/2018 RMAS
9/1/2018 10/25/2018 RMAS
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 RBEL
9/1/2018 10/25/2018 BILL

**J_Marley** · 11-05-2018, 11:05 AM

Hi Jaslake,

Thanks for taking a look at this with me. Your first post is correct, with a slight detail to add. If multiple checkboxes are selected (Retail & Indirect, or Business and Retail) it would filter to show the data for both. Blanks would be excluded, as well as RMAS, RBELL, and BILL as those belong to data we would not need for the audience intended.

As for the mock up of how this would look, I wasn't sure how exactly one would mock up a filter on multiple items. I have included a workbook with the Retail and Indirect checkboxes selected and those columns are filtered to match.

**jaslake** · 11-07-2018, 08:35 PM

Hi J_Marley
Sorry for the late follow-up...it's Fall...ton of leaves...have collected ~30 bags in the last two weeks...8 today...getting toward the end...

In the attached I've added a Worksheet "Channels"...you will need to maintain this...adding elements to Columns "Retail", "Indirect" and "Business" as required. When adding elements to Column "Business", assuming they are all numeric as in your sample data, be certain to add a single leading apostrophe to the entry...eg... 9999 should be entered as '9999.
Assuming entries to "Retail" and "Indirect" are always prefaced with an Alpha these should work fine.

I've only addressed the "Filter Channels" Button on Sheet "Run Report" and the output is a Filtered Range on Sheet "Data Feed". Play with it, see if it does as required, we can add bells and whistles...

Code for Channel_Filter...

Option Explicit
Sub Channel_Filter()
    Dim arrCriteria As Variant
    Dim LR As Long
    Dim ChkBox As Excel.CheckBox
    ActiveWorkbook.Names.Add Name:="Retail", RefersTo:= _
                             "=OFFSET(Channels!$A$2,0,0,(COUNTA(Channels!$A:$A)-1),1)"
    ActiveWorkbook.Names.Add Name:="Indirect", RefersTo:= _
                             "=OFFSET(Channels!$B$2,0,0,(COUNTA(Channels!$B:$B)-1),1)"
    ActiveWorkbook.Names.Add Name:="Business", RefersTo:= _
                             "=OFFSET(Channels!$C$2,0,0,(COUNTA(Channels!$C:$C)-1),1)"

    With Sheets("Channels")
        .Range("F1").Value = "Filter Criteria"
        LR = .Range("F" & .Rows.Count).End(xlUp).Row + 1
        .Range("F2:F" & LR).ClearContents
    End With

    With Sheets("RunReport")
        For Each ChkBox In .CheckBoxes
            If ChkBox.Value = 1 Then
                With Sheets("Channels")
                    LR = .Range("F" & .Rows.Count).End(xlUp).Row + 1
                    .Range(ChkBox.Caption).Copy
                    .Range("F" & LR).PasteSpecial (xlPasteValues)
                End With
            End If
        Next ChkBox
        ActiveWorkbook.Names.Add Name:="FilterCriteria", RefersTo:= _
                                 "=OFFSET(Channels!$F$2,0,0,(COUNTA(Channels!$F:$F)-1),1)"
    End With

    ReDim arrCriteria(0)
    arrCriteria = Application.Transpose(Range("FilterCriteria"))

    Sheets("DataFeed").ListObjects("Table_Tablix1").Range.AutoFilter Field:=16, Criteria1:=Array(arrCriteria), Operator:=xlFilterValues

    'Below are the autofilters for each channel. They are commented out until a connection is made to their corsponding checkbox.
    'RETAIL range_to_filter.AutoFilter Field:=16, Criteria1:=Array("A020", "A040", "A050", "A090", "A100", "A110", "A130", "A140", "B100", "B200", "B300", "B400", "B500", "B600", "B700", "B800", "B900", "C200", "C300", "C400", "E100", "E200", "F200", "F300", "F400", "F500", "K100", "K200", "K400", "K800", "L200", "N100", "N200", "N300", "P100"), Operator:=xlFilterValues
    'Indirect range_to_filter.AutoFilter Field:=16, Criteria1:=Array("Z700", "Z705", "Z708", "Z710", "Z711, "Z712", "Z716", "Z718", "Z721", "Z722", "Z724", "Z726", "Z727", "Z728", "Z730", "Z732", "Z734", "Z735", "Z740", "Z741", "Z742"), Operator:=xlFilterValues
    'BUSINESS range_to_filter.AutoFilter Field:=16, Criteria1:=Array("001", "0160", "1014", "1019", "1024", "1026", "1136", "1183", "1362", "2016", "2017", "2702", "2703", "3261", "3262", "3267", "3333", "3342", "4013", "4134", "4151", "5012", "5100", "5101", "5109", "6003", "6005", "6017", "6104"), Operator:=xlFilterValues

End Sub

**J_Marley** · 11-13-2018, 05:52 PM

Hi jaslake,

Thank you so much for the help! Sorry for not responding as projects have come up which moved my priorities. This is great and works nearly perfectly. The only snag I came across is when selecting more than one check box to run the filter with, it jumps to the Channels sheet. It is not a big deal in this use, I just change back to the sheet I am looking for, but if I am using this in a much larger capacity, it could cause confusion.

I was able to create a macro around the filter reset and have the rest of the connections and all working as well. Again, thank you so much for everything!

**jaslake** · 11-13-2018, 05:59 PM

Hi J_Marley

Yeah, the attached was simply for your review to see if it does as required in terms of Filtering.
I'll fix this this...

The only snag I came across is when selecting more than one check box to run the filter with, it jumps to the Channels sheet

Are there any other "Bells and Whistles" you require?

**J_Marley** · 11-13-2018, 06:02 PM

No, this is great. I know it might seem pretty simple and basic from your perspective, but from someone with a background in graphic design and marketing, the reporting world isn't as easy to grasp.

**jaslake** · 11-13-2018, 06:11 PM

I'll make some minor Code Mods such as error checking, screen updating and such and will post the revised code sometime this evening.

**jaslake** · 11-13-2018, 08:02 PM

Hi J_Marley

Replace the Channel Filter Code with this...it Filters Sheet Data Feed with the Channels selected and with the Range of Dates Selected. It also keeps you on Sheet Run Report...not sure where you wish to be...you'll need to tell me...simple change/addition. Let me know of issues.

Option Explicit
Sub Channel_Filter()
    Dim arrCriteria As Variant
    Dim LR As Long
    Dim x As Long
    Dim ChkBox As Excel.CheckBox

    Application.ScreenUpdating = False
    ActiveWorkbook.Names.Add Name:="Retail", RefersTo:= _
                             "=OFFSET(Channels!$A$2,0,0,(COUNTA(Channels!$A:$A)-1),1)"
    ActiveWorkbook.Names.Add Name:="Indirect", RefersTo:= _
                             "=OFFSET(Channels!$B$2,0,0,(COUNTA(Channels!$B:$B)-1),1)"
    ActiveWorkbook.Names.Add Name:="Business", RefersTo:= _
                             "=OFFSET(Channels!$C$2,0,0,(COUNTA(Channels!$C:$C)-1),1)"

    With Sheets("Channels")
        .Range("F1").Value = "Filter Criteria"
        LR = .Range("F" & .Rows.Count).End(xlUp).Row + 1
        .Range("F2:F" & LR).ClearContents
    End With

    With Sheets("RunReport")
        For Each ChkBox In .CheckBoxes
            If ChkBox.Value = 1 Then
                With Sheets("Channels")
                    LR = .Range("F" & .Rows.Count).End(xlUp).Row + 1
                    .Range(ChkBox.Caption).Copy
                    .Range("F" & LR).PasteSpecial (xlPasteValues)
                End With
            End If
        Next ChkBox
        ActiveWorkbook.Names.Add Name:="FilterCriteria", RefersTo:= _
                                 "=OFFSET(Channels!$F$2,0,0,(COUNTA(Channels!$F:$F)-1),1)"
    End With

    'Check to see if one or more Channels are selected...if none are selected x will equal 0
    On Error Resume Next
    x = Range("FilterCriteria").Rows.Count
    On Error GoTo 0

    'If x is NOT equal to 0 at least 1 Channel was selected
    If Not x = 0 Then
        ReDim arrCriteria(0)
        arrCriteria = Application.Transpose(Range("FilterCriteria"))
        With Sheets("DataFeed")
            'Filter on Channels Selected
            .ListObjects("Table_Tablix1").Range.AutoFilter Field:=16, Criteria1:=Array(arrCriteria), Operator:=xlFilterValues

            'Filter on Start Date
            .ListObjects("Table_Tablix1").Range.AutoFilter Field:=1, _
                                                           Criteria1:=">=" & ActiveSheet.Range("F13").Value, Operator:=xlAnd
            'Filter on End Date
            .ListObjects("Table_Tablix1").Range.AutoFilter Field:=2, _
                                                           Criteria1:="<=" & ActiveSheet.Range("K13").Value, Operator:=xlAnd
        End With
    Else
        'if no Channels are selected
        MsgBox "Please select one or more Channels"
    End If
    Application.ScreenUpdating = True
End Sub

Filter one column with multiple checkboxes on multiple values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Re: Filter one column with multiple checkboxes on multiple values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Filter one column with multiple checkboxes

Macro to filter multiple values in a column

Filter one column with multiple checkboxes to multiple data tables

[SOLVED] Multiple Checkboxes to filter

[SOLVED] Pivot Chart - Want to filter a column with multiple values

updating multiple pivot tables with multiple filter values

filter for multiple column values

Bookmarks

Bookmarks

Posting Permissions