Use slicer to select a pre-determined set of filters at once?

**scottmc13** · 02-21-2023, 01:53 PM

Hi,

What is the best way to select a set of pre-determined filters at once? I think I want to use slicers but I can only see how to select one filter at once?

I have uploaded a very simple table as an example with two columns: Job and Location. I basically want to push one button to show all jobs with Locations a to d, and then be able to push another button to show locations e to h, and another that will display only jobs i to k. I'd like this to be applied to the existing table, rather than generating a new table with this info.

Is this possible? I have used buttons with macros on my actual sheet to do this just now, but it gets messy and only works if i press them in the correct order/ and I need to reset the filters etc.

Thanks

**ORoos** · 02-28-2023, 12:39 AM

Hi there,

I think macros are the simplest solution, using autoFilters.

In the attached we are using AutoFilters based on column 2 and specify the values you want to filter by.
You can filter by the 3 buttons in any order. No need to reset filters between selections. Plus a 4th button to un-filter.

Hope this helps.

Sub SetA()

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        Array("a", "b", "c", "d"), Operator:=xlFilterValues
End Sub

Sub SetB()

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        Array("e", "f", "g", "h"), Operator:=xlFilterValues
        
End Sub

Sub SetC()

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
        Array("i", "j", "k"), Operator:=xlFilterValues

End Sub
Sub FilerALL()

    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
    
End Sub

**scottmc13** · 03-01-2023, 07:02 AM

Yes this is the only solution I can find to do this too, I was hoping that there would be a more automated way of building it but it appears there isn't.

Thanks for your help.

Use slicer to select a pre-determined set of filters at once?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Use slicer to select a pre-determined set of filters at once?

Re: Use slicer to select a pre-determined set of filters at once?

Re: Use slicer to select a pre-determined set of filters at once?

Thread Information

Users Browsing this Thread

Similar Threads

Detect changes to slicer filters for slicers connected to a table

Macro to run filters with slicer

Newbie: Auto Select/un-Select an Range of Slicer Items

Make one slicer mirror filters of another slicer

Select range size determined by value in cell

Selecting Slicer filters from a cell/cells

Select Row determined by Variable

Bookmarks

Bookmarks

Posting Permissions