MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA All Slicer Combinations


June 30, 2017 - by Bill Jelen

VBA All Slicer Combinations

Regular pivot table filters offer the Show All Report Filter pages, but Slicers do not support this functionality. Today, some VBA to loop through all possible slicer combinations.

Watch Video

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2106 create a PDF of every combination
  • of three slicers what a great question
  • we have today someone wrote in want to
  • know if it was possible right now they
  • have three slicers running a pivot table
  • I don't know what the pivot table looks
  • like it's confidential not allowed to
  • see it
  • so I'm just guessing right so what
  • they're doing is they're choosing one
  • item from each slicer and then creating
  • a PDF and then going and choosing the
  • next item and creating a PDF and then
  • the next item and the next item and you
  • can imagine with 400 combinations of
  • slicers this could take forever they
  • said is there some way to have a program
  • go through and and loop through all the
  • options I said all right here's some
  • qualifying questions number one we're
  • not on a Mac right not Android not Excel
  • for the iPhone this is Excel for Windows
  • yes they said great it's a second really
  • important question is we want to choose
  • one item from a slicer and then
  • eventually the other item from the
  • slicer and then the other end from the
  • slicer we don't need combinations like
  • Andy and then Andy and Betty and then
  • Andy and Charlie all right that's out
  • I'm just going to do one item from each
  • slicer yes yes yes that's the way is
  • going to go perfect is it all right so
  • here tell me this choosie slicer go to
  • the slicer tools options and go to
  • slicer settings we just did this two
  • episodes ago isn't this crazy name views
  • in four minutes I know that it's slicer
  • underscore reviewer slicer underscore
  • antenna slicer underscore discipline all
  • right so I think I think I've got it now
  • we're going to switch over to VBA here
  • by the way make sure that your saves is
  • xlsm and make sure your macro security
  • is set to allow macros if it saves as
  • xlsx trust me you have to go do a file
  • save as it's you're going to lose all
  • your work if you leave it as xlsx yes
  • 99.9% of the spreadsheet to use or xlsx
  • but this one with a macro will not work
  • f11 all right so here's the code we're
  • going to find three slicer caches one
  • slicer item and three ranges for each of
  • the slicer caches we're going to set it
  • to the name used in formula just showed
  • you in the slicer settings dialog box so
  • we have the three of those when I clear
  • all those to make sure that we're back
  • to everything being selected this
  • counter is going to used in the file
  • name later
  • all right now this next section here off
  • to the right build three static lists of
  • all slicer items see I'll take number
  • two to see why this craziness had it
  • happen so I'm going to figure out where
  • the next available column is kind of go
  • over to from the last column
  • remember that's like and delete the
  • stuff later and then for each si slicer
  • item and SC 1 dot slicer items we're
  • going to write that slicer caption to
  • the spreadsheet when we're done with all
  • of those slicer items figure out how
  • many rows we have today and then name
  • that range of slicer items 1 we're going
  • to repeat that whole thing for slicer
  • cash two going over one column slicer
  • items two and slice around three let me
  • show you what it looks like at this
  • point so I'll put a breakpoint right
  • here and we will run this code all right
  • that was fast we're going to switch over
  • to VBA and far off here to the
  • right-hand side I'm gonna get three new
  • lists these lists or everything that's
  • in the slicer and you see it's called
  • slicer items one slicer items two and
  • slicer items three all right we will get
  • rid of that at the end but that gives us
  • something to loop through back to VBA
  • all right we're going to loop through
  • all the items in slicer items one clear
  • the filter for slicer cash one and then
  • we're going to go through one at a time
  • through each slicer item and see if this
  • slicer item is equal to this sell one
  • value again we're looping through each
  • of the values so the first time through
  • is going to be an D and then Betty and
  • you know and so on it's frustrating I
  • couldn't find any way to turn all the
  • slicers off at once I even tried
  • recording the code and choosing one
  • slicer and the recorded code was
  • returning nine places off and turning
  • the one slicer on all right
  • so frustrating I couldn't find any
  • better than that but I couldn't find any
  • better than that
  • so we set the first lexer equal to an d
  • then we go through and for the second
  • place that we're in said to be equal to
  • the first item for the third slicer so
  • equal to the first item alright then
  • down here decide if this is a valid
  • combination
  • I got explained to you why that's
  • important
  • we as humans we're doing this Andy we
  • would not choose a 52 because clearly
  • it's grayed out but the macro is going
  • to be too stupid and it's going to
  • choose a 52 and then 104 and it's going
  • to create this empty pivot table so
  • there's a thousand possible combinations
  • here I know that there's only four
  • hundred possible reports that's what the
  • person told me and so that we're going
  • to get 600 times where we're going to
  • create a PDF of this ugly report so what
  • I'm going to do is I'm gonna look here
  • on the analyze tab it was called options
  • in 2010 and see what the name of this
  • pivot table is and I want to see how
  • many rows we get my case if I get two
  • rows I know it's a report I don't one
  • export if I get more than two rows three
  • four five six then I know it is a report
  • that I do want to export you're gonna
  • have to figure out in your situation
  • which it is alright so that's why we're
  • checking to see if the pivot table - and
  • that's the name on it that was back
  • there in the ribbon table range two dot
  • rows dot count is greater than two if
  • it's not greater than two we don't want
  • to create a PDF all right so this if
  • statement down to this end if is saying
  • we're going to create the PDF for the
  • report combinations that have values my
  • file name I created a folder called C
  • colon reports it's just an empty folder
  • C colon reports you make sure that you
  • have a folder and use the same folder
  • name in the macro C colon reports slash
  • and the name of the file is going to be
  • report
  • backup there is one using format which
  • is equivalent in Excel to saying the
  • text of counter and zero zero zero that
  • way I'm going to get 0 0 1 and 0 0 2
  • then 0 0 3 and then 0 0 4 they're going
  • to sort correctly if I just call this
  • report 1 and then later on I have a
  • report 10 and 11 and later on report 100
  • are all going to sort together when they
  • don't belong together
  • all right so creating the name of the
  • file in case the file exists from the
  • last time we ran this we're going to
  • kill it
  • in other words delete it of course if
  • you try and kill a file that's not there
  • they will throw an error so if we get an
  • error in the next line that's fine just
  • go on but then I reset the error
  • checking on error go to 0 here's the
  • active sheet export is fixed format as a
  • PDF there's the file name all those
  • choices and then I increment the counter
  • so that way next
  • next time we find one that has records
  • will be creating report Oh - dot PDF
  • finish those three loops and then clear
  • out the static lists I'll remember which
  • column we were resize one row three
  • columns entire columns clear and then a
  • nice little message box there to show
  • that things have been created okay let's
  • run it all right now what should be
  • happening here is if we go and look in
  • Windows Explorer there it is okay it is
  • creating like every second we're getting
  • two or three or four or more I'll pause
  • this and let it run alright there we are
  • 326 reports have been created that loop
  • through all 1000 possibilities and only
  • kept the ones where there was an actual
  • result all right from 938 until 942 four
  • minutes to do all that but still faster
  • than doing the 400 right alright so
  • that's the macro way to do this the
  • other thing that struck me here is it
  • may or may not work it's really tough to
  • say let's take our data and I'm going to
  • move the data to a brand new workbook
  • mover copy create a copy to a new book
  • click OK I'm going to use a trick here
  • that I first learned from Sylvia Yuhas
  • great Excel consultant out in Southern
  • California and we're going to add a key
  • field here the key field is equal
  • reviewer ampersand antenna ampersand
  • discipline we'll copy that down and
  • we'll insert a new pivot table
  • click okay and we're going to take that
  • filled the key field and move it up to
  • the old-fashioned filters and then let's
  • see let's dispel a report here reviewer
  • antenna discipline and revenue like that
  • alright now normally what we would do
  • here is would come open this filter and
  • choose one item from the filter but the
  • trick from Sylvia is that we can take
  • this pivot table and go to either the
  • analyze tab and 13 or 16 are the options
  • to have in 2010 open the options trapped
  • say show report filter pages show all
  • pages of key and what it's doing right
  • now is its inserting a new worksheet for
  • every unique combination of the key and
  • probably 300 and some files all right
  • now how many worksheets can you have a
  • workbook all that number is different on
  • every computer and it depends on how
  • complicated the workbook is because it's
  • limited by available memory but here we
  • start on andyb three seven one one two
  • I'm going to press ctrl and this arrow
  • down to Jo like that the beautiful
  • advantage here is why do file export
  • create a PDF and then all reports where
  • I end up with a single PDF with all 326
  • reports in it now we could have created
  • a single PDF using Adobe Acrobat select
  • all these reports right click and
  • combine files in Acrobat but that
  • requires you have a full version of a
  • threat not just a Gravette reader so
  • this great trick using show report
  • filter pages from Sylvia might be a
  • great great alternative if you have
  • enough memory to create all the versions
  • alright to learn more about VBA check
  • out this book Excel 2016 VBA and macros
  • I'm Bill Jelen and Tracy's here cent
  • I'll get you up the VBA learning curve
  • all right the goal is to loop through
  • all combinations and three slicers and
  • generate a PDF reach used a little VBA
  • to loop through the slicers save as PDF
  • using VBA the alternate solution there
  • at the end is sylvia houses show report
  • filter pages
  • export the whole things PDF hey I want
  • to thank you for stopping by we'll see
  • you next time for another neck test
  • MrExcel well this will be an outtake
  • first time I ran this darn thing I got a
  • thousand of them and every darn one of
  • them was Andy a 52 104 I'm like what the
  • heck is going on
  • bidam's AHEC alright so here watch this
  • code this was a go ahead I said I'm
  • going to go through all of the filters
  • for each Si and si dot one less lesser
  • notice I set it equal to false and then
  • the one that I want
  • I'll set equal to true right sound like
  • a great great bit of code alright so
  • here's what happens the first one is
  • Andy goes away Betty goes away Charlie
  • goes away Dale I'll just keep pressing
  • f8 f8f8f8 I'm down to the last one this
  • is Jo I'm about to set Jo equal to false
  • and watch what happens over there in
  • Excel Pam once you turn Jo up it turns
  • them all back on I mean that's stinks
  • Excel and then I would try and turn what
  • is it Andy back on and turning Andy back
  • on when everybody else is already hon so
  • it ran through it created a thousand for
  • the BBS ever you stinking one it was
  • Andy a fifty two 104 it's funny now I
  • think it wasn't then all right here's
  • another outtake why did I go to the
  • trouble of building the list the static
  • list off to the right hand side so I can
  • loop sure that static list well Ridge
  • '''l I was looping through all of the
  • items in the slicers themselves and it
  • was causing some wrong results see here
  • Andy 852 112 should be 0 but when I
  • actually ran the loop and the a 52 112
  • is showing up with six rows how Michael
  • that can't be so over here my code all
  • that's 11 I put a thing if si one
  • caption equals Andy si to captions
  • equals a 52 si 3 caption equals 1/12
  • then stop right so let's run this code
  • and stop there we are and I will come
  • back we should have an da 52 112
  • but when I look Andy it's not a 52 it's
  • d-33 what the heck is going on and then
  • I come back here all f11 and I
  • right-click and say that I want to add a
  • watch and when I look at this it claims
  • that the caption is a 52 but very
  • clearly it's D 33 so is this a bug or am
  • I just violating some weird rule by
  • looping through a collection of ten
  • items when the order of those ten items
  • is constantly being reordered it seems
  • like that must be the problem hence we
  • went with the static list off to the
  • right and the third outtake alright this
  • is the one that's crazy if I want to
  • record a macro if I want to write a
  • macro root choose just one item figure
  • out how to do that by using developer
  • record macro how to choose one item from
  • slicer click OK and we simply choose one
  • item flow click stop recording and we go
  • all that fate how to choose one I'm from
  • slicer edit that and sure enough they
  • make flow true and then everybody else
  • false means if I had a slicer with a
  • hundred items in it they would have to
  • put a hundred lines of code in there to
  • unselect everything else seems
  • incredibly inefficient but there you are

Download File

Download the sample file here: Podcast2106.xlsx

Title Photo: Braite / Pixabay


Bill Jelen is the author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.