Filtering Pivots based on external ranges.

Howdy, folks. Jeff here again. We might as well rename this blog Daily Dose of Pivot Tables, because here’s yet another treatment of this well-worn subject this week.

Let’s say you’ve got a PivotField with 20,000 items in it. What’s the quickest way to filter that PivotTable based on an external list that contains either 100, 10000, or 19900 of those items?
The usual approach to a task like this is to just iterate through each pivotitem in the PivotItems collection, and check if that PivotItem is in the list of search terms. If it is, you make the PivotItem visible, otherwise you hide it.

But this requires us to:

  • Read the PivotItem name
  • Read in the Search Terms
  • Check if each PivotItem is in the list of search terms. If it is, you make the PivotItem visible, otherwise you hide it.

How long does each part of this take? Where are the bottlenecks? Let’s find out.

First, how long does it take just to iterate through all 20,000 items and get the value of each PivotItem? (Note that I’ve set pt.ManualUpdate = True before running all the following snippets, so that the PivotTable doesn’t try to update after each and every change)


For Each pi In pf.PivotItems
strName = pi.Value 'In my test data, all items are strings
Next

Not long at all. Under a second.

How long to work out what the current visiblestatus is of each PivotItem?


For Each pi In pf.PivotItems
bStatus = pi.Visible
Next

One minute and twenty seconds? Really? Not exactly lightning fast, is it?

Okay, how long does it take to set the .Visible property of each item, without checking what it currently is? Let’s set .visible = true for each and every PivotItem (even though they are already visible) just to find out what the worst case scenario is.


For Each pi In pf.PivotItems
pi.Visible = True
Next

Two minutes, 43 seconds. So it takes longer to set the .visible status than to read it. Handy to know.

Okay, how long will it take to first check the .Visible property of each item, and then change it?


For Each pi In pf.PivotItems
pi.Visible = Not pi.Visible
Next

Four minutes, 26 seconds. Not surprising I guess, because it’s got to first find the current state of each item – which we already established above takes around one minute and twenty seconds – and then we need to change the state – which we already established above takes around 2 minutes 43 seconds. And those two times add up to 4 minutes.

So that’s how long it would take in principle to filter the PivotTable based on the initial approach I suggested above, excluding the time taken to actually check for duplicates between the PivotItems and the search terms.

Ahhh… I hear you say (I have good ears). What if we first check whether the .visible status of a PivotItem is already set how we want it. That way, we can save some time by only changing it if it actually needs to be changed. Good point, and nice to see you’re alert.

So here’s our efficient tweak of the ‘traditional’ method:

  • Add all Search terms to a dictionary (or collection, if you prefer)
  • Try to add each PivotItem to that same dictionary.
  • If that last step caused an error, we’ll know that this PivotItem is in our list of search terms. In this case, we can check what the current visible status is of the PivotItem. If it’s NOT visible, we’ll make it visible. If it IS visible, we do nothing
  • IF this didn’t cause an error, we’ll know that this PivotItem IS NOT in our list of filter terms. In this case, we again check what the current visible status is of the PivotItem. If it’s visible, we’ll hide it. If it’s already hidden, we do nothing

So this approach is quite efficient in that it only changes the .visible status of the PivotItem if it has to. Which is good, because this is the bottleneck. And the general approach of using a Dictionary (or collection) is very efficient, compared to other ways I’ve seen on line that use say applicaiton.match to check the PivotItem against a variant array or (far worse) against the original FilterTerms range in the worksheet.

On a pivot of 20,000 items that currently has all items visible, here’s how this ‘tweaked traditional’ method performed:

  • It took 4:21 to filter on a list of 100 terms. When I ran it again without clearing the filter, it only took 1:32. That faster time is because it didn’t have to change the .visible status of any items at all, because they were already in the ‘correct’ state after the last run. But it still had to check them all
  • It took 3:03 to filter on a list of 10,000 terms. The shorter time compared to the first test case is because it only had to set the .visible status of half the pivot items. It took 1:35 when I ran it again without clearing the filter, same as before. That’s what I would expect.
  • It took 1:35 again to filter on a list of 19900 items – the same as the 2nd pass in the other cases, which again is what I would expect given it only had to hide a few items. And of course it took about the same time again when I ran it again without clearing the filter, same as before.

(Note that my ‘tweaked traditional’ routine has some extra checks to handle errors caused by dates in PivotFields not formatted as Date Fields, something I discussed here. And it also has to do some extra checking for PivotItems such as “1.1”, because VBA’s IsDate function interprets such a string as a date. But the extra processing time of these extra loops is pretty inconsequential compared to checking and changing the .Visible status.)

Can we do better than that?

Of course we can. What if we work out how many PivotItems in the PivotField, and how many search terms in the Search list, and either make all PivotItems visible or all PivotItems (bar one) hidden before we start, so that we minimise the amount of PivotItems we have to change the .Visible status of?

  • If there’s just 100 items in our Search Terms list, hide all but one PivotItem, then unhide just the 100 matching items
  • If there’s 19900 items in our Search Terms list, make all PivotItems visible, then hide the 100 PivotItems that are not in the Search Terms list
  • Because we know in advance whether all PivotItems are visible or hidden, we don’t have to check their .visible status at all.

Genius in theory, I know. And it’s certainly trivial to clear a PivotFilter so that all items are visible in that 2nd case. But that 1st case is tricky: how do you hide all but one PivotItem via VBA without iterating through and having to do all that incredibly slow .visible = false stuff? You can do it manually very easily of course. But via VBA? You can’t do it directly except if you make the field a Page Field and set .EnableMultiplePageItems to False. And then as soon as you change it to True again, VBA helpfully clears the filter so that all items are visible again. And so you’re back to square 1.

Enter the slicer

It turn out that you can very quickly hide all but one PivotItem programatically if you make a temp copy of the Pivot, make the field of interest in the temp into a Page field with .EnableMultiplePageItems set to False, and then hook it up via a slicer to your original Pivot. This forces the original PivotField to have the same filter setting – just one item visible. But it doesn’t make that original Pivot have the same layout. So the original pivot can still be say a Row field where you can then merrily make additional items visible.

How fast is this approach? Very. Again, using a test pivot with 20,000 items in it:

  • Filter on 100 search terms: 0:05 (compared with 4:21 in the approach above)
  • Filter on 10,000 search terms: 1:26 (compared with 3:03 in the approach above)
  • Filter on 19,900 search terms: 0:03 (compared with 1:35 in the approach above)

Now that is some improvement.

Here’s the two routines below for your viewing pleasure. I turned both routines into functions, which you call by a wrapper. This lets you pre-specify what PivotField you want to filter and where your search terms are. Otherwise you’ll be prompted to select them via some input boxes.

Also check out the attached workbook that has the code inside, and that lets you generate random alphanumeric PivotFields and Search Terms in a jiffy (something I’ll cover in a future post). Just click the Create Sample Data button after changing the input parameters, and then click on the command button of choice. When you run the code from the command buttons, the times of each pass will be recorded in the workbook too, so you can compare different settings.

Filter PivotTable 20131114

Have at it people. Look forward to comments, feedback, suggestions, and Nobel Prize nominations.

Regards,
Jeff

Slower Approach


Sub Dictionary_Slower()
FilterPivot_Dictionary_Slower
End Sub

Private Function FilterPivot_Dictionary_Slower(Optional rngPivotField As Range, Optional rngFilterItems As Range) As Boolean
‘ Copyright ©2013 Jeff Weir
‘ weir.jeff@gmail.com
‘ You are free to use this code within your own applications, add-ins,
‘ documents etc but you are expressly forbidden from selling or
‘ otherwise distributing this source code without prior consent.
‘ This includes both posting free demo projects made from this
‘ code as well as reproducing the code in text or html format.
‘ ———————————————————————

‘ Date Initial Details Version
‘ 20131113 JSW Initial Programming 007 (of course)

‘#############
‘# Remarks #
‘#############

‘ This code needs to be called by a wrapper function.
‘ e.g.

‘ Sub FilterPivot()
‘ FilterPivot_Dictionary_Slower
‘ End Sub

‘ If required, that wrapper function can also provide ranges
‘ specifying what PivotField to filter, and where the range of
‘ filter terms is. e.g.:
‘ FilterPivot_Dictionary_Slower Range(“A2”), Range(“C2:C20000”)
‘ …or
‘ FilterPivot_Dictionary_Slower(ActiveCell, [tblFilterItems])

Dim ptOriginal As PivotTable
Dim pfOriginal As PivotField
Dim pfFilterItems As PivotField
Dim pi As PivotItem
Dim ptFilterItems As PivotTable
Dim wksTemp As Worksheet
Dim wksPivot As Worksheet
Dim dic As Object
Dim varContinue As Variant
Dim strMessage As String
Dim varFormat As Variant
Dim bDateFormat As Boolean
Dim bDateWarning As Boolean

FilterPivot_Dictionary_Slower = False ‘Assume failure

On Error GoTo ErrHandler
Set wksPivot = ActiveSheet

‘If neccessary, prompt user for the pivotfield of interest
If rngPivotField Is Nothing Then
On Error Resume Next
Set rngPivotField = ActiveCell
Set pfOriginal = rngPivotField.PivotField ‘Tests if this is in fact a PivotField
If Err <> 0 Then
Err.Clear
Set rngPivotField = Nothing
Set rngPivotField = Application.InputBox( _
Title:=”Where is the PivotField?”, _
Prompt:=”Please select a cell in the PivotField you want to filter”, _
Type:=8)
On Error GoTo ErrHandler
If rngPivotField Is Nothing Then Err.Raise 996
End If
On Error GoTo ErrHandler
End If

Set pfOriginal = rngPivotField.PivotField
Set ptOriginal = pfOriginal.Parent

‘If neccessary, prompt user for FilterItems table related to the pivotfield of interest
If rngFilterItems Is Nothing Then
On Error Resume Next
Set rngFilterItems = Application.InputBox( _
Title:=”Where are the filter items?”, _
Prompt:=”Please select the range where your filter terms are”, _
Type:=8)
On Error GoTo ErrHandler
If rngFilterItems Is Nothing Then Err.Raise 996
End If

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

‘ Excel stores dates differently between PivotItems and Variant Arrays.

‘ For instance:
‘ ? CStr(varFilterItems(i, 1))
‘ 1/01/2013
‘ ? pi.Value
‘ 1/1/2013
‘ ? CStr(varFilterItems(i, 1)) = pi.Value
‘ False

‘So we ‘ll turn our FilterItems into a PivotTable to ensure formats are treated the same.

Set wksTemp = Sheets.Add
rngFilterItems.Copy wksTemp.Range(“A2”)
wksTemp.Range(“A1”).Value = “FilterItems”
Set rngFilterItems = wksTemp.Range(“A2″).CurrentRegion

On Error GoTo 0

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rngFilterItems).CreatePivotTable _
TableDestination:=[C1], TableName:=”appFilterItems”

Set ptFilterItems = wksTemp.PivotTables(“appFilterItems”)
Set pfFilterItems = ptFilterItems.PivotFields(1)

‘ Add FILTERItems to a Dictionary
Set dic = CreateObject(“scripting.dictionary”)
For Each pi In pfFilterItems.PivotItems
dic.Add pi.Value, 1 ‘The one does nothing
Next

ptOriginal.ManualUpdate = True ‘dramatically speeds up the routine, because the pivot won’t recalculate until we’re done

‘Check if PFOriginal is formatted as a date field.
‘ Basically there is a bug in Excel whereby if you try to do some things
‘ to a PivotItem containing a date but the PivotField number format is NOT a date format
‘ then you get an error.
‘ So we’ll check the PivotField date format and see what it is
‘ Note that if a PivotField is based on a range that contains multiple formats
‘ then you get an error simply by checking what the PivotField number format is.
‘ So we’ll instigate an On Error Resume Next to handle this

On Error Resume Next
varFormat = pfOriginal.NumberFormat
On Error GoTo ErrHandler
If IsDate(Format(1, varFormat)) Then bDateFormat = True

‘ Now try and add the PivotItems.
‘ If ther’s an error, we’ll know that this item is also in the FilterTerms
On Error Resume Next
With dic
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 ‘The 1 does nothing
If Err.Number <> 0 Then
‘This item exists in our search term list, so we should unhide it
‘Note that IF this item is a date but the PivotField format is NOT a date format,
‘ we can’t programatically hide/show items, so we’ll have to check this first
If Not bDateFormat Then
If Not IsNumeric(pi.Value) Then
‘We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g.”1.1″ are dates
If IsDate(pi.Value) Then
If Not bDateWarning Then
On Error GoTo ErrHandler
Err.Raise Number:=997, Description:=”Can’t filter dates”
On Error Resume Next
End If
Else:
If Not pi.Visible = True Then pi.Visible = True
End If
Else: If Not pi.Visible = True Then pi.Visible = True
End If
Else: If Not pi.Visible = True Then pi.Visible = True
End If
Err.Clear
Else: If Not pi.Visible = False Then pi.Visible = False
End If
Next
End With

On Error GoTo ErrHandler
FilterPivot_Dictionary_Slower = True

ErrHandler:
If Err.Number <> 0 Then
Select Case Err.Number
Case Is = 0: ‘No error – do nothing
Case Is = 996: ‘Operation Cancelled
Case Is = 997: ‘Can’t filter dates
strMessage = “*** WARNING…I can’t correctly filter dates in this Pivot ***”
strMessage = strMessage & vbNewLine & vbNewLine
strMessage = strMessage & “I’ve found at least one date in this PivotField. ”
strMessage = strMessage & “Unfortunately due to a bug in Excel, if you have dates ”
strMessage = strMessage & ” in a PivotField AND that PivotField is NOT formatted ”
strMessage = strMessage & ” with a date format, then dates ”
strMessage = strMessage & ” can’t be programatically filtered either in or out. ”
strMessage = strMessage & vbNewLine & vbNewLine
strMessage = strMessage & ” So you’ll have to manually check to see whether ”
strMessage = strMessage & ” date items appear as they should.”
strMessage = strMessage & vbNewLine & vbNewLine
strMessage = strMessage & “Do you want me to continue anyway? ”
varContinue = MsgBox(Prompt:=strMessage, Buttons:=vbYesNo, Title:=”Sorry, can’t filter dates”)
If varContinue = 6 Then
bDateWarning = True
Resume Next
Else: pfOriginal.ClearAllFilters
End If
Case Is = 998: ‘Can’t filter Datafields
MsgBox “Oops, you can’t filter a DataField.” & vbNewLine & vbNewLine & “Please select a RowField, dicumnField, or PageField and try again.”, vbCritical, “Can’t filter Datafields”
Case Is = 999: ‘no pivotfield selected
MsgBox “Oops, you haven’t selected a pivotfield.” & vbNewLine & vbNewLine & “Please select a RowField, dicumnField, or PageField and try again.”, vbCritical, “No PivotField selected”
Case Else:
MsgBox “Whoops, something went wrong”
End Select
End If

With Application
If Not wksTemp Is Nothing Then
.DisplayAlerts = False
wksTemp.Delete
.DisplayAlerts = True
End If
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
If Not ptOriginal Is Nothing Then ptOriginal.ManualUpdate = False

End Function

Faster Approach


Option Explicit

'Further Development Ideas
' Date Ini Detail
' 20150605 JSW Colud potentially enhance the handling of dates in GENERAL formatted PivotFields by checking if format matches the US date layout.
' See http://www.vbforums.com/showthread.php?287517-Determining-regional-setting-in-VBA-code
' ALso, there's a potential workaround at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/#comment-638479

Sub FilterPivot()
' Description: Wrapper function for my FilterPivot Function
' (Required in the event that a user wants to trigger the FilterPivot
' function and specify parameters at runtime, instead of programatiacally
' calling the routine)
' Programmer: Jeff Weir
' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz

' Name/Version: Date: Ini: Modification:
' FilterPivot 20150305 JSW Initial programming

FilterPivot_Routine
End Sub

Sub FilterPivot_Inverse()
' Description: Wrapper function for my FilterPivot_Inverse Function
' (Required in the event that a user wants to trigger the FilterPivot_Inverse
' function and specify parameters at runtime, instead of programatiacally
' calling the routine)
' Programmer: Jeff Weir
' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz

' Name/Version: Date: Ini: Modification:
' FilterPivot_Inverse 20150305 JSW Initial programming
FilterPivot_Routine bInverse:=True
End Sub

Function FilterPivot_AddRightClick()

' Description: Adds "Filter Pivotfield" shortcuts to PivotTable right-click menu
' Programmer: Jeff Weir
' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz

' Name/Version: Date: Ini: Modification:
' AddShortcuts 20150305 JSW Initial programming

Dim cbr As CommandBar
Dim sMessage As String
FilterPivot_RemoveRightClick
Set cbr = Application.CommandBars("PivotTable Context Menu")

With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Filter Pivotfield"
.Tag = "FilterPivotField"
.OnAction = "FilterPivot"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("FilterAdvancedByForm", 16, 16)
End With
With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True)
.Caption = "Inversely Filter Pivotfield"
.Tag = "FilterPivotField"
.OnAction = "FilterPivot_Inverse"
.Style = msoButtonIconAndCaption
.Picture = Application.CommandBars.GetImageMso("FilterAdvancedMenu", 16, 16)
End With

End Function

Function FilterPivot_RemoveRightClick()

' Description: Removes "Filter Pivotfield" shortcuts from PivotTable right-click menu
' Programmer: Jeff Weir
' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz

' Name/Version: Date: Ini: Modification:
' AddShortcuts 20150305 JSW Initial programming

Dim cbr As CommandBar
Dim sMessage As String
Dim ctrl As CommandBarControl

Set cbr = Application.CommandBars("PivotTable Context Menu")
For Each ctrl In cbr.Controls
If ctrl.Tag = "FilterPivotField" Then ctrl.Delete
Next

End Function

Private Function FilterPivot_Slicers(ptTemp As PivotTable, pfTemp As PivotField, ptOriginal As PivotTable)
Dim sc As SlicerCache
' This sub-function contains code that requires Slicers
' It's in this stand-alone function, becaues it only gets called if user has Excel 2010 or later
' If it was incorporated DIRECTY within the FilterPivot function, that function wouldn't compile on pre 2010 machines

Set sc = ActiveWorkbook.SlicerCaches.Add(ptTemp, pfTemp)
sc.PivotTables.AddPivotTable ptOriginal
'Great, our original pivot now just has one item visible in the field of interest
'So we can delete the slicer connection
sc.Delete
End Function

Private Function FilterPivot_Routine(Optional rngPivotField As Range, Optional rngFilterItems As Range, Optional bInverse As Boolean = False) As Boolean
' Copyright ©2013 Jeff Weir
' weir.jeff@gmail.com
' You are free to use this code within your own applications, add-ins,
' documents etc but you are expressly forbidden from selling or
' otherwise distributing this source code without prior consent.
' This includes both posting free demo projects made from this
' code as well as reproducing the code in text or html format.
' ---------------------------------------------------------------------

' Date Initial Version Details

' 20131113 JSW 007 Initial Programming
' 20131203 JSW 008 Added Inverse Option
' 20140503 JSW 009 Changed so works in pre Excel 2010
' 20140503 JSW 010 Changed so pi.format check not performed in Excel 2013 +
' 20150223 JSW 011 Added check to ensure pfOriginal has .EnableMultiplePageItems set to TRUE if it is a pagefield.
' 20150429 JSW 012 Turned off events, cleared pfOriginal filter.
' 20150605 JSW 013 Fixed “Save source data with file” reset as per http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/#comment-787682

'#############
'# Remarks #
'#############

' This code needs to be called by a wrapper function.
' e.g.

' Sub FilterPivot()
' FilterPivot_Routine
' End Sub

' Sub FilterPivot_Inverse()
' FilterPivot_Routine bInverse:=True
' End Sub

' If required, that wrapper function can also provide ranges
' specifying what PivotField to filter, and where the range of
' filter terms is. e.g.:
' FilterPivot_Routine Range("A2"), Range("C2:C20000")
' ...or
' FilterPivot_Routine ActiveCell, [tblFilterItems]

Dim ptOriginal As PivotTable
Dim ptTemp As PivotTable
Dim pfOriginal As PivotField
Dim pfTemp As PivotField
Dim pfFilterItems As PivotField
Dim lngFilterItems As Long
Dim pi As PivotItem
Dim ptFilterItems As PivotTable
Dim wksTemp As Worksheet
Dim wksPivot As Worksheet
Dim dic As Object
Dim varContinue As Variant
Dim strMessage As String
Dim varFormat As Variant
Dim bDateFormat As Boolean
Dim bDateWarning As Boolean
Dim bFirstItemVisible As Boolean
Dim varFirstItemVisible As Variant
Dim bEnableEvents As Boolean
Dim bScreenUpdating As Boolean
Dim lngCalculation As Long
Dim bSaveDataState As Boolean

FilterPivot_Routine = False 'Assume failure

On Error GoTo errhandler
Set wksPivot = ActiveSheet

'If neccessary, prompt user for the pivotfield of interest
If rngPivotField Is Nothing Then
On Error Resume Next
Set rngPivotField = ActiveCell
Set pfOriginal = rngPivotField.PivotField 'Tests if this is in fact a PivotField
If Err <> 0 Then
Err.Clear
Set rngPivotField = Nothing
Set rngPivotField = Application.InputBox( _
Title:="Where is the PivotField?", _
Prompt:="Please select a cell in the PivotField you want to filter", _
Type:=8)
On Error GoTo errhandler
If rngPivotField Is Nothing Then Err.Raise 996
End If
On Error GoTo errhandler
End If

Set pfOriginal = rngPivotField.PivotField
Set ptOriginal = pfOriginal.Parent

'Capture the SaveData state of the original pivot table
bSaveDataState = ptOriginal.SaveData

With pfOriginal
If .Orientation = xlPageField Then
If .EnableMultiplePageItems = False Then
.EnableMultiplePageItems = True
.ClearAllFilters
End If
End If
End With

'If neccessary, prompt user for FilterItems table related to the pivotfield of interest
If rngFilterItems Is Nothing Then
On Error Resume Next
Set rngFilterItems = Application.InputBox( _
Title:="Where are the filter items?", _
Prompt:="Please select the range where your filter terms are.", _
Type:=8)
On Error GoTo errhandler
If rngFilterItems Is Nothing Then Err.Raise 996
End If

With Application
bScreenUpdating = .ScreenUpdating
bEnableEvents = .EnableEvents
lngCalculation = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

' Excel stores dates differently between PivotItems and Variant Arrays.

' For instance:
' ? CStr(varFilterItems(i, 1))
' 1/01/2013
' ? pi.Value
' 1/1/2013
' ? CStr(varFilterItems(i, 1)) = pi.Value
' False

'So we 'll turn our FilterItems into a PivotTable to ensure formats are treated the same.

Set wksTemp = Sheets.Add
rngFilterItems.Copy wksTemp.Range("A2")
wksTemp.Range("A1").Value = "FilterItems"
Set rngFilterItems = wksTemp.Range("A2").CurrentRegion

On Error GoTo errhandler

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rngFilterItems).CreatePivotTable _
TableDestination:=[C1], TableName:="appFilterItems"

Set ptFilterItems = wksTemp.PivotTables("appFilterItems")
Set pfFilterItems = ptFilterItems.PivotFields(1)

' Add FILTERItems to a Dictionary
Set dic = CreateObject("scripting.dictionary")
For Each pi In pfFilterItems.PivotItems
dic.Add pi.Value, 1 'The one does nothing
Next

ptOriginal.ManualUpdate = True 'dramatically speeds up the routine, because the pivot won't recalculate until we're done

'Check if we're dealing with Excel 2010 or earlier and if PFOriginal is formatted as a date field.
' Basically there is a bug in Excel 2010 or earlier whereby if you try to do some things
' to a PivotItem containing a date but the PivotField number format is NOT a date format
' then you get an error.
' So we'll check the PivotField date format and see what it is
' Note that if a PivotField is based on a range that contains multiple formats
' then you get an error simply by checking what the PivotField number format is.
' So we'll instigate an On Error Resume Next to handle this

If Application.Version < 15 Then On Error Resume Next varFormat = pfOriginal.NumberFormat On Error GoTo errhandler If IsDate(Format(1, varFormat)) Then bDateFormat = True End If 'Work out maximum amount of filteritems under bInverse scenario If bInverse Then lngFilterItems = pfOriginal.PivotItems.Count - rngFilterItems.Count Else: lngFilterItems = rngFilterItems.Count End If If lngFilterItems / pfOriginal.PivotItems.Count < 0.5 And Application.Version >= 14 Then

'====================================================================================
' If it's likely that less than half of the source Pivot Field's
' items will be visible when we're done, then it will be quickest to hide all but one
' item and then unhide the PivotItems that match the filter terms

' Iterating through a large pivot setting all but one item to hidden is slow.
' And there's no way to directly do this except in Page Fields, and
' that method doesn't let you select multiple items anyway.
' Plus, as soon as you drag a page field with just one item showing to
' a row field, Excel clears the filter, so that all items are visible again.

' So we'll use a trick:
' * make the pf of interest in ptTemp a page field
' * turn off multiple items and select just one PivotItem
' * connect it to the original pivot with a slicer
' This will very quickly sync up the field on the original pivot so that only one field is showing.

' NOTE: WE CAN ONLY DO THIS IF USING EXCEL 2010 OR LATER using the below approach.
' If earlier version, we'll use the approach outlined in the ELSE part of this IF block.

' ALSO NOTE: If a PivotField has a non-Date format, but contains dates, then
' we can't programatically hide/show items. So we need to check for this.
'====================================================================================

'Identify a suitable item with which to filter the original PivotTable with
' As per note above,
' * If the PivotField format is NOT a date format,
' then we need to make sure that this first item is NOT a date.
' ...because otherwise we can't address it by VBA
' * If the PivotFied format IS a date format, then just use the first item.
' * We'll write that item to a range, then to a variant, so that Excel applies the
' same format to it as it does to items in our Filter list

If Not bDateFormat Then
For Each pi In pfOriginal.PivotItems
If IsDate(pi.Value) Then
If IsNumeric(pi.Value) Then
'We need the IsNumeric bit above because
'VBA thinks that some decimals encased in strings e.g. "1.1" are dates
'So we need to check whether this is a decimal and NOT a date
varFirstItemVisible = pi.Value
Exit For
Else:
If Not bDateWarning Then
Err.Raise Number:=997, Description:="Can't filter dates"
End If
End If
Else:
varFirstItemVisible = pi.Value
Exit For
End If
Next
Else:
varFirstItemVisible = pfOriginal.PivotItems(1).Value
End If

Set ptTemp = ptOriginal.PivotCache.CreatePivotTable(TableDestination:=wksTemp.Range("F1"))
Set pfTemp = ptTemp.PivotFields(pfOriginal.SourceName)

'Set the SaveData state the same as the original pivot table in case it is set to False
'When creating a new pivot table, by default it is set to True, which will carry over to the original pivot table
ptTemp.SaveData = bSaveDataState

With pfTemp
.Orientation = xlPageField
.ClearAllFilters
.EnableMultiplePageItems = False
.CurrentPage = pfTemp.PivotItems(varFirstItemVisible).Value
End With

Call FilterPivot_Slicers(ptTemp, pfTemp, ptOriginal)

' Check if FirstItemVisible should be visible or hidden when we are done
If dic.Exists(varFirstItemVisible) Then bFirstItemVisible = True

' Now try and add the PivotItems.
' If there's an error, we'll know that this item is also in the FilterTerms
On Error Resume Next
With dic

'The Not bInverse bit in the code blocks below effectively 'flip' the test "If Err.Number <> 0" to "If Err.Number = 0"
'in the case that bInverse argument is TRUE (meaning we want the Pivot to be filtered on things
' NOT in the list of search terms)

If Application.Version >= 15 Then
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 'The 1 does nothing
If Err.Number <> 0 = Not bInverse Then
pi.Visible = True
End If
Err.Clear
Next
Else: 'There's a bug in previous versions where you can't use .Visible for dates
'if the PivotField is set to General format.
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 'The 1 does nothing
If Err.Number <> 0 = Not bInverse Then
' This item exists in our search term list, so we should unhide it
' Note that due to a bug in Excel 2010, if this item is a date
' but the PivotField format is NOT a date format, we can't
' programatically hide/show items, so we'll have to check this first
If Not bDateFormat Then
If Not IsNumeric(pi.Value) Then
'We need the Not IsNumeric bit above because VBA thinks that
' some decimals encased in strings e.g."1.1" are dates
If IsDate(pi.Value) Then
If Not bDateWarning Then
On Error GoTo errhandler
Err.Raise Number:=997, Description:="Can't filter dates"
On Error Resume Next
End If
Else: pi.Visible = True
End If
Else: pi.Visible = True
End If
Else: pi.Visible = True
End If
End If
Err.Clear
Next
End If 'If Application.Version >= 15 Then

End With

If Not bFirstItemVisible = Not bInverse Then
pfOriginal.PivotItems(varFirstItemVisible).Visible = False
If Err.Number <> 0 Then
MsgBox "None of the filter items were found in the Pivot"
pfOriginal.ClearAllFilters
Err.Clear
End If
End If

Else:
' If it's likely that MORE than half of the source Pivot Field's items will be visible
' when we're done, then it's quickest to unhide all PivotItems and then hide the
' PivotItems that DON'T match the filter terms

pfOriginal.ClearAllFilters

' Now try and add the PivotItems.
' If there's an error, we'll know that this item is in the FilterItems
' Otherwise we'll hide it

On Error Resume Next
With dic
If Application.Version >= 15 Then 'Excel 2010 and earier have some 'issues' with dates
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 'The 1 does nothing
If Err.Number = 0 = Not bInverse Then pi.Visible = False
Err.Clear
Next
Else:
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 'The 1 does nothing
If Err.Number = 0 = Not bInverse Then
'The Not bInverse bit effectively 'flips' the test "If Err.Number = 0" to "If Err.Number <> 0"
'in the case that bInverse argument is TRUE (meaning we want the Pivot to be filtered on things
' NOT in the list of search terms)

'This PivotItem NOT in FilterItems list. So hide it
'Note that IF this item is a date but the PivotField format is NOT a date format,
' then we can't programatically hide/show items, so we'll have to check this first
If Not bDateFormat Then
If Not IsNumeric(pi.Value) Then
'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
If IsDate(pi.Value) Then
If Not bDateWarning Then
On Error GoTo errhandler
Err.Raise Number:=997, Description:="Can't filter dates"
On Error Resume Next
End If
Else: pi.Visible = False 'This item does not exist in the FilterItems. So hide it
End If
Else: pi.Visible = False 'This item does not exist in the FilterItems. So hide it
End If
Else: pi.Visible = False
End If
End If
Err.Clear
Next
End If 'If Application.Version >= 15 Then
End With

End If 'If lngFilterItems / pfOriginal.PivotItems.Count < 0.5 And Application.Version >= 14 Then
On Error GoTo errhandler
FilterPivot_Routine = True

errhandler:
If Err.Number <> 0 Then
Select Case Err.Number
Case Is = 0: 'No error - do nothing
Case Is = 996: 'Operation Cancelled
Case Is = 997: 'Can't filter dates
strMessage = "*** WARNING...I can't correctly filter dates in this Pivot ***"
strMessage = strMessage & vbNewLine & vbNewLine
strMessage = strMessage & "I've found at least one date in this PivotField. "
strMessage = strMessage & "Unfortunately due to a bug in Excel, if you have dates "
strMessage = strMessage & " in a PivotField AND that PivotField is NOT formatted "
strMessage = strMessage & " with a date format, then dates "
strMessage = strMessage & " can't be programatically filtered either in or out. "
strMessage = strMessage & vbNewLine & vbNewLine
strMessage = strMessage & " So you'll have to manually check to see whether "
strMessage = strMessage & " date items appear as they should."
strMessage = strMessage & vbNewLine & vbNewLine
strMessage = strMessage & "Do you want me to continue anyway? "
varContinue = MsgBox(Prompt:=strMessage, Buttons:=vbYesNo, Title:="Sorry, can't filter dates")
If varContinue = 6 Then
bDateWarning = True
Resume Next
Else: pfOriginal.ClearAllFilters
End If
Case Is = 998: 'Can't filter Datafields
MsgBox "Oops, you can't filter a DataField." & vbNewLine & vbNewLine & "Please select a RowField, ColumnField, or PageField and try again.", vbCritical, "Can't filter Datafields"
Case Is = 999: 'no pivotfield selected
MsgBox "Oops, you haven't selected a pivotfield." & vbNewLine & vbNewLine & "Please select a RowField, ColumnField, or PageField and try again.", vbCritical, "No PivotField selected"
Case Else:
MsgBox "Whoops, something went wrong"
End Select
End If

With Application
If Not wksTemp Is Nothing Then
.DisplayAlerts = False
wksTemp.Delete
.DisplayAlerts = True
End If
.ScreenUpdating = bScreenUpdating
.EnableEvents = bEnableEvents
.Calculation = lngCalculation

End With
If Not ptOriginal Is Nothing Then ptOriginal.ManualUpdate = False

End Function

62 thoughts on “Filtering Pivots based on external ranges.

  1. Hi Steve. So I take it your Pivot must be based on an OLAP data source for this to work? I get a run-time error 1004 (Application-defined or object-defined error) when I try and run your example in your sample file in Excel 2010.

  2. Hi David. Yep, great approach if you have PowerPivot installed. Where I work, we’re not allowed to install it even though we have the requisite license, I’m afraid. And of course the more people that adopt 2013, the less that will have PowerPivot under the current licensing agreement.

  3. Hi Doug. Forgot to mention that approach, which is pretty bulletproof, assuming the pivot’s source data is in the same workbook. If the data is not in the same workbook, I guess you would have to extract the data using the ol’ doubleclick on the Grand Total method, then re-point the Pivot at the extracted data, then set up the file as you’ve done.

    Thanks for reminding me. I see I left an earlier version of my code in your comments way back then. The beauty about the above code approach is that it is lightning fast to execute – no setup time at all. So it sits in my Personal Macro Workbook just itching for a Pivot to cross it’s path. And in most cases I imagine that users will be filtering on a search list of just a few hundred items at most, which takes just a few seconds. And because I turned it into a function, I can use it as part of larger routines pretty effortlessly.

  4. Hi Jeff,

    Looks very interesting. Your line:

    ptOriginal.ManualUpdate = True ‘dramatically speeds up the routine, because the pivot won’t recalculate until we’re done

    sent me off on a tangent. I hope you don’t mind me asking this question here as slicers aren’t available in Excel 2003.

    I have many Excel 2003 VBA processes that would benefit from this – if it worked. The official documentation for 2003 says the property is read/write but I can’t seem to change it from False at all. I’ve been googling all afternoon to see if anyone has a definitive answer but to no avail. I suppose if it doesn’t work for 2003 at least it wouldn’t do any harm and would yield benefits for later versions. Do you have any experience of this or do you know of any alternative approaches to .ManualUpdate that would work for people with Excel 2003?

  5. I’m pretty sure that the docementation is wrong – that line should work just fine in 2003.

    I have a version of this code that does pretty much the same thing without slicers. There’s a few issues with it, but I might be able to sort them. If I do, I’ll post it here.

  6. Jeff,

    Just to be clear, the 2003 documentation says .ManualUpdate is read/write. So the documentation says that ptOriginal.ManualUpdate should work just fine in 2003. If the documentation is wrong the line will not work in 2003. That seems to be what I’m experiencing – .ManualUpdate seems to be read-only and permanently set to False.

    Great work Jeff. I really appreciate the time you and others put into posts such as this one. So much to learn!

  7. Thanks, Ian. You’re right there’s a lot to learn. Which is why I tinker away at projects like this one. This code I’ve been working on in some shape or form for several years, and it’s only now that I’m starting to feel that I’m fluent in VBA. Mostly I learn by constant testing, tinkering, and then recoding. And occasionally I go back through the archives of resources like this blog to have a fresh look at stuff that was over my head last time I checked.

  8. Jeff,

    unfortunately in Excel 2013 – whenever “Case Is < 0.5" – the fast version yields error 1004 on line "sc.PivotTables.AddPivotTable ptOriginal". PivotField is Region or Customer.

  9. Jeff,

    without really knowing what I’m doing, I have added a line to your code. Now, it works just fine. A real masterpiece!

    Set sc = ActiveWorkbook.SlicerCaches.Add(ptTemp, pfTemp)
    ADDED: sc.PivotTables.RemovePivotTable ptTemp
    sc.PivotTables.AddPivotTable ptOriginal

    btw, I would also change
    Select Case rngFilterItems.Count / pfOriginal.PivotItems.Count
    to
    Select Case (rngFilterItems.Count – 1) / pfOriginal.PivotItems.Count
    as CurrentRegion includes the heading

  10. Hi Frank. I don’t get the same error as you. Can you email me a sample dataset where you get this issue to weir.jeff@gmail.com so I can take a closer look?

    That said, testing did find another issue whereby if there is already a slicer in the workbook for the field of interest, that slicer ends up being disconnected. So I’ll make a suitable revision and post back here.

  11. With regards to your Select Case (rngFilterItems.Count – 1) / pfOriginal.PivotItems.Count tweak, this isn’t really neccessary… I’m just after the rough ‘breakeven’ point where it will likely be faster to do something one way vs the other, and that rough breakeven point will be roughly around the point where we are trying to filter more than or less than half. But the exact breakeven point will depend on what kind of data is in the pivot and some other factors. So being exactly precise isn’t necessary.

  12. Pingback: Filter PivotTable
  13. GMoney: I already have ptOriginal.ManualUpdate = True in both routines. In fact, right at the top of the actual writeup I mention Manual Update too:

    First, how long does it take just to iterate through all 20,000 items and get the value of each PivotItem? (Note that I’ve set pt.ManualUpdate = True before running all the following snippets, so that the PivotTable doesn’t try to update after each and every change)

    If you can write faster code to do this, I’d love to see it.

  14. If I understand you correctly:
    – you want the range in column C to be filtered by the items in column E.

    As far as I can see that can be performed by:

    Sub M_snb()
    t1 = Timer

    With Sheet1.ListObjects("tblpivotdata").Range
    .AutoFilter 1, Filter(Application.Transpose(Sheet1.Range("E11:E110")), ""), 7
    .SpecialCells(12).Copy Sheet1.Cells(10, 9)
    .AutoFilter
    End With

    MsgBox Timer - t1
    End Sub

    Or did I overlook the essence of your point ?

  15. No. I want to filter the PivotTable in Column G based on the items is Column E.

    Column C is just the source data for the pivottable in Column G.

    This whole post is about filtering PivotTables quickly.

  16. I tried to reduce your code:

    The randomized strings of 10 alphanumeric characters each:

    Sub M_snb()
    Randomize
    ‘ c00=”0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz”
    c00 = Join(Filter([transpose(if(row(48:122)96,char(row(48:122)),if(mod(row(48:122),65)>26,char(row(48:122))))))], False, False), “”)

    ReDim sp(20000, 0)
    For j = 0 To UBound(sp)
    sp(j, 0) = Join(Array(Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1)), “”)
    Next

    ListObjects(“tblPivotdata”).DataBodyRange = sp
    ListObjects(“tblFilterItems”).DataBodyRange = sp
    PivotTables(“appPivot”).RefreshTable
    End Sub

    The code to filter the Pivottable (I created the Slicer manually before):

    Sub M_snb_001()
    sn = ListObjects(“tblFilterItems”).DataBodyRange

    With ActiveSheet.PivotTables(“appPivot”)
    .ManualUpdate = True

    With ActiveWorkbook.SlicerCaches(1)
    .ShowAllItems = True
    .SlicerItems(sn(1, 1)).Selected = True

    For j = 2 To UBound(sn)
    .SlicerItems(sn(j, 1)).Selected = True
    Next
    End With

    .ManualUpdate = False
    End With
    End Sub

  17. snb: Yes, your code is much shorter than mine. But my code is not longer just for the sake of it.

    • On 1000 filter terms, my long code took 36 seconds. Your short code took 1:42.
    • On 4000 filter terms, my long code took 2:09. Your short code took 9:41.
    • Try my code on say 19900 filter terms. You’ll see that it executes just as fast as if you were filtering just 100 terms. Try the same with yours, and you’ll have to quit Execl.

    What’s more, my code is point and click. It doesn’t have object hard wired into it like yours does, meaning the user can select whichever PivotField and list of search terms they want. Any level of user can use my code without modification. And my code warns users about a potential bug regarding Dates, as outlined in the article above. Whereas yours bombs out.

    You have reduced my code to the point that it doesn’t do half of the things my code is supposed to do. It no longer robustly filters a PivotField under a range of realistic scenarios.

    I just don’t understand the point of putting up shorter code for the sake of it.

  18. Hi Jeff,

    I’ve been testing with your code on Excel 2010, and I have to say the speed is awesome (0:11 against 2:21 earlier)!

    However (you saw that coming, right? ;-), for some reason the pivot field shows the first item of the multiple items selected (and the data related to that single item) in stead of “(Multiple Items)”.
    Only when I click on the drop-down icon of that field and then just click OK (nothing else changes), the pivot field shows “(Multiple Items)” and the data reflects all visible items.

    Pivot table refreshing doesn’t help. I even cleared & rebuilt the whole pivot table in case there was a corruption of sorts, but it still didn’t help.

    Any idea how I can fix that?

    Thanks,
    Rudi

  19. Ah, forgot to mention that unless I set my original pivot field to “EnableMultiplePageItems = True” before running your macro, it will not work. Is that normal?

  20. Hi Jeff,

    Solved it; sorry to have bothered you.
    My pivot filtering uses both single choice and multiple choice filters, with the PageField option mucking things up.

    Setting ‘EnableMultiplePageItems = True’ partially solved it, but the missing ingredient was ‘ClearAllFilters’.
    My only excuse is that I must have been worn out after a whole day programming and debugging…

    In case someone else encounters the problem, here’s what I did:
    After: ‘Set ptOriginal = pfOriginal.Parent’ I added the following code (I heavily use pivot event driven code):

    Best regards,
    Rudi

  21. Hi Rudi. Glad to have sped things along for you. I’ve reworked this code significantly. I’ll email you a copy of my latest if you don’t mind, because I’d like to see if my revised code still suffers from whatever issue you were having.

  22. @All: Have amended the Faster Approach code in line with some of Rudi’s suggestions to me. (Some were already made…I just haven’t updated the code for a while.) Also added some routines that set up a right-click option in the PivotTable Context menu.

  23. Hi Jeff,

    I noticed that using this procedure, the “Save source data with file” option was reset.
    Since the data I use has to be refreshed every time I use the spreadsheet, I turned that option off. It also keeps my file size nice and small.

    Turns out that by creating a temporary pivot table, which has the “SaveData” property by default set to TRUE, that setting also carries back over to the original pivot table. All other settings like “Refresh data when opening the file” and “Number of items to retain per field” seem to be unaffected.

    Here’s my solution in case you’re interested (I included parts of the original code to show where it goes):

    1. Declare the SaveDataState variable

    2. Save the “SaveData” state of the original pivot table

    3. Set the “SaveData” state of the temporary pivot table

    That’s it… No more unauthorized changes and bloated workbooks :-)

  24. Rudi – have updated the code, and also made another slight tweak:
    I added this IF:

    …around this:

    I also added note at the top around a further development idea I had re handling an excel bug I’ve previously discussed at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems

    If you’re at a loose end, maybe you want to take a look ;-)

  25. Jeff,

    Somehow I either never had to use dates in the pivots I used with regional Excel installations or the installations were US version based, anyway I never encountered that long running problem.

    At the moment I’m working with an Excel 2010 with regional settings for Belgium and I tried setting up the pivot table (no problem there) and running the code mentioned in the other blog posting “A date with PivotItems”:

    Trying to step through the code results in a “Run-time error ‘1004’: Application-defined or object-defined error” running

    (first time) or

    (second time) after changing the numberformat to “General”.

    Stepping through the code multiple times the error keeps flip-flopping between the first and second line after

    . I haven’t got a clue why that happens…

    As such, I could not confirm the results of the “Immediate Window” after the change to “General” and I also don’t see the name of the pivot field changing.

  26. Hello Jeff,

    I just love your code – I think it is amazing how good it works. I am using the faster approach and now I am trying to set the ranges so the user does not have to select the pivot field and the items. However, I am having issues doing this. I have tried several options, but either the Excel crashes or the error pops-up. Might you be please able to help me?

    I have tried these:
    FilterPivot_Routine ActiveSheet.Range(“C8”), ActiveSheet.Range(“J3:J4”)
    FilterPivot_Routine Range(“C8”), Range(“J3:J4”)
    FilterPivot_Routine ActiveWorkbook.Worksheets(“Account”).Range(“C8”), ActiveWorkbook.Worksheets(“Account”).Range(“J3:J4”)

    Thanks a lot

  27. Hello Jeff,

    I’m trying to use your code on a Pivot table (from an OLAP source) with 83K items in the selected filter field (I want to apply between 100 to 500 unique items to the filter), all in Excel 2010. I’m getting an Err value of 1004 at the line:

    If Not bDateFormat Then
    here–> For Each pi In pfOriginal.PivotItems
    If IsDate(pi.Value) Then

    The code at this point pauses (evaluating?) for about 10s and then errors (Err.Number is 1004) without moving to the If statement. Any thoughts on what might be causing this issue?

    Cheers
    Craig

  28. Craig: The bad news is this code doesn’t work on OLAP Pivots. The good news is that I have some code that does. The bad news is I can’t share that code, because I’m in the process of turning it into a commercial addin. The good news is that I’m turning it into a commercial addin that you can buy soon. (That’s good news for both of us). The bad news is that I can’t give you an exact time it will be available, because I’m still in the bug fixing stage (and have been for some time). The good news is that I’m sure I’ll have it out in the next month or two…sooner if you don’t mind using a beta version.

    The addin also lets you do multiple wildcard filtering of PivotTables and Tables on the fly, and also lets you invert your current filter selection. It’s a cross between the existing PivotTable filter options, a slicer, and the advanced filter. It’s pretty cool, if I do say so myself.

    I’ll flick you an email in due course with a few screenshots.

  29. Hi Jeff,
    this is great! Many thanks for the code.
    But as some of the others I will need the OLAP version, how is the progress with that and where can I buy it?
    Many thanks
    Jakub

  30. Hi Jakub. I’ve just finished a six month contract and so have finally got some spare time to put into finishing the addin. But I’m on holiday at present, plus might have a week long contract lined up already on my return. So the earliest I’m likely able to deliver a prototype is a fortnight or more. Will flick you sn email on my return.

  31. Awesome macro man, I totally don’t get why excel won’t build in “(Select All)”.Visible = False, but at least you found a pretty decent work around. I was able to use your code fairly successfully, however I have 1 question I can’t seem to solve. I would like to filter on partial values, like in your example file you have strings of 10 characters, but say I only know 5 of the 10 in string, can the macro be adapted to do that? Any help would be appreciated, and thanks again for the big boost in the first place!

  32. One other question I have, I have 2 different fields in the columns area of my pivot table, I would like the filter to check both, but so far I can only have it check one at a time, is it possible for the macro to check through both ranges?

  33. Well I basically answered the 2 questions I had, the first, by creating a macro to set up an table based on an auto filter copy of the column I have the partial matches for, then using that table which then had the full matches (for my filter items), I ran the macro above. Secondly, I didn’t need to run the two fields separately, so I just created one macro for each condition, which ended up working out perfect. But now I am left with another question as I try and improve the output. I have the original table that was used to set up the filtering of the pivot table, but say I want to go into and manually edit the filters to add or remove some. What I would like to have then is to re-extract the filter values back to update that table (or in a newly created one is fine too). Anyone have any idea on how to extract the filter selections into a table?

  34. Hi Randy. Thanks for your feedback. That is tricky. But you’re in luck: I’m working on an addin that does just that. It allows you to do wildcard filtering on PivotTables, so you can look for partial matches et cetera, and you can look for as many at a time as you like. The UI I’ve put together to do this is very cool, but it’s very tricky to accomplish behind the scenes, and as per my answer to Jakub’s question, I’m not quite finished coding it up yet.

    I’ve had to put it on hold again because I’ve signed up for a short term contract. Once that contract is done and dusted, I’ll pick it up again, and will flick you an email when I get close to having the beta available.

  35. Thanks Jeff for the reply. I am using your macro very successfully now, but have had some interesting results I am trying to work around. I have been reading up on slicers quite a bit after seeing how you used them here and really like the visual aspect of them. I have added them to my sheet. I did have to comment out the line deleting the slicer connections, as it broke the link to the slicers I have set up on the template. I have 2 slicers setup, for example, say one to filter on color (red, yellow, green) and another to filter on type (apples, peppers, bananas, potatoes). If I run the macro to filter on “red”, apples and peppers and potatoes will show (and I don’t know of any red bananas), I can extract the visible slicer items for the color (red) however, if I use the visible slicer items for the type, all of them still show up, including bananas, even though they won’t show in the table. The “apples”, “peppers” and “potatoes” will show in the slicer as dark blue, and the “bananas” will show as light blue, while in the color slicer “red” will show as dark blue, and “yellow” and “green” will be white (as they are not visible, per the macros inputs of “red”). I have found in the slicer settings the “hide items with no data” and I thought this was the trick, but while it makes the light blue items disappear from the slicer, they do not disappear from the slicer visible items list. Anyway, is not too desirable for me as it is, because after seeing the slicers, I would like to use them to add (or remove) items and revise the pivots. Any thoughts on how to get only the “dark blue” items from a the slicer data? Many thanks for all the work you have put into this already!

  36. Forgot to mention that for some reason, I have named my slicers using the Name Manager, and after running the macro, the slicer that has the column of data that had the filter items name changes back to a default name. Any idea why? I’m combing though your code looking for some reason, but can’t yet see one.

  37. Hi Randy. I think the add-in I’m working on solves your issue, but that’s assuming I’m understanding your description above. Probably best you flick me an email at weir.jeff@gmail.com with a sample file.

  38. Jeff

    Wondering if you have used the Slicer Cache property of RequireManualUpdate and if you have any sample code to understand how to assign and use this property?
    I have some code to swap various fields around on some pivot tables and have 5 slicers connected to several pivot tables and charts, but the code is very slow to swap the fields around once the slicers are connected. Without connecting them everything updates within 2 seconds, connecting them changes the run time to 20 seconds…

  39. No I haven’t used it. Will have a play. I wrote a follow-up post on poor performance when filtering pivots with slicers that Google will point you to, called “Filtering Pivots? Disconnect Slicers first!”

  40. Hi Jeff,

    I wanted to thank you again for this macro. I have used it very successfully. I do have one question that I have yet to solve. In semi-rare instance that there is no items found from the source found in the pivot data, I would prefer the pivot field select “(blank)”, rather than leave everything. I am trying to tweak you code do add that in, but keep breaking it. Any thoughts on how to add that in?

    Thanks again!

  41. Hi Randy. Good idea, but would only work if the pivot data source indeed had a (blank) field in it. Is that the case for your data? I’m unlikely to spend any time changing the above code to do this, because I have been working on a significantly revised version as part of the add-in I’m forever finishing. I put the development of that addin on hold about two months back while I worked on other projects, but it’s probably time I got back to it. Let me know if you’re interested in buying a trial version at the above email address. It’s pretty cool…it’s just not *quite* ready for public consumption yet, as I’m also tweaking it so that it handles pivots based on OLAP data sources, which is holding me up a little.

  42. I get a compile error with the the section from “If Application.Version < 15 Then" highlighted in red
    Has anyone else fathomed out if there is a problem with an easy solution?

  43. Hi Angela. I think that in some countries the version gets returned as a string. What happens if you wrap a VAL around that?
    e.g.
    If Val(Application.Version) < 15

Leave a Reply

Your email address will not be published. Required fields are marked *