Grafit: The two code snippets you have posted do very different things. The first one makes everything visible except for the value "(leeg)". The second one makes any item with "BSO" in it visible, and hides everything else. There are problems with both bits of code.
In regards to your first code snippet, if you want to show every item except for the one called "(leeg)" then there is no need to iterate though the PivotItems collection (which is really slow on large Pivots). Instead, simply do this:
pf.ClearAllFilters
pf.PivotItems("leeg").visible = false
In regards to your second bit of code, then yes the error could be caused by the MissingItemsLimit issue, but it also will occur if the code tries to hide a PivotItem while no other PivotItems are currently visible during the loop. For instance, if you had the PivotTable filtered on say just one item such as "Aardvark", then because "Aardvark" does not have "BSO" in it, the code will attempt to hide it, and will then error out, because at least one PiovtItem must remain visible at all times.
So what you have to do is add a line before the loop that makes the last item in the PivotItems collection visible, so that you can pretty much guarantee that one item will remain visible right up to the end of the loop.
(Of course, if "BSO" does not appear in ANY of the PivotItems, then you'll still get the error when you go to process that last item).
Furthermore, whenever you iterate through the PivotITems collection you usually want to set PT.ManualUpdate to True, so that the PivotTable doesn't try to update the totals in the PivotTable after each and every item gets hidden/unhidden. Then at the end of the routine you set PT.ManualUpdate to False again, which then tells Excel "I'm done...you can update these PivotTable totals now." This usually makes a staggering difference in terms of speed of your routine. On large Pivots you will save A LOT of time.
I wrote an article that discusses this stuff in depth at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ that I suggest you check out.
--edit-- Here's a routine to clear the PivotTable so that only one item is displayed:
Sub FilterPivot_PivotItem(pfOriginal As PivotField, _
Optional pi As PivotItem, _
Optional pfTemp As PivotField, _
Optional bDelete_wksTemp As Boolean = True, _
Optional bDelete_ptTemp As Boolean = False)
' If pfOriginal is a PageField, we'll simply turn .EnableMultipleItems to FALSE
' and select pi as a PageField
' Otherwise we'll
' * create a temp copy of the PivotTable
' * Make the field of interest a PageField
' * Turn .EnableMultipleItems to FALSE and select pi as a PageField
' * Add a Slicer to that PageField
' * Connect that Slicer to pfOriginal, which will force it instantly to sync.
' to pfTemp, meaning it shows just one item
' This is much faster than Iterating through a large PivotTable and setting all but
' one item to hidden, as outlined at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
Const sRoutine = "FilterPivot_PivotItem"
Dim sc As SlicerCache
Dim bSlicerExists As Boolean
Dim ptOriginal As PivotTable
Dim ptTemp As PivotTable
Dim wksTemp As Worksheet
Dim bDisplayAlerts As Boolean
Dim lCalculation As Long
Dim bEnableEvents As Boolean
Dim bScreenUpdating As Boolean
Dim TimeTaken As Date
TimeTaken = Now()
Set ptOriginal = pfOriginal.Parent
With Application
bScreenUpdating = .ScreenUpdating
bEnableEvents = .EnableEvents
lCalculation = .Calculation
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
With pfOriginal
If pi Is Nothing Then Set pi = .PivotItems(1)
If .Orientation = xlPageField Then
'Great: we're dealing with a field in the FILTERS pane, which let us
' select a singe item easily
.EnableMultiplePageItems = False
.CurrentPage = pi.Name
Else
' For non PageFields we'll have to use a temp PivotTable and Slicer to quickly clear
' all but one PivotItem.
'Check if pfOriginal already has a slicer connected
' If so, then we'll want to leave it in place when we're done
bSlicerExists = Slicer_Exists(ptOriginal, pfOriginal)
' A temp PivotTable may aleady exist and have been passed in when the function was called
' Otherwise we'll need to create one.
If pfTemp Is Nothing Then
Set wksTemp = Sheets.Add
Set ptTemp = ptOriginal.PivotCache.CreatePivotTable(TableDestination:=wksTemp.Range("A1"))
Set pfTemp = ptTemp.PivotFields(.SourceName)
'Set the SaveData state of this new PivotTable the same as the original PivotTable
'(By default it is set to True, and is passed on to the original PivotTable when a Slicer is connected)
If ptTemp.SaveData <> ptOriginal.SaveData Then ptTemp.SaveData = ptOriginal.SaveData
Else
Set ptTemp = pfTemp.Parent
'Check if pfTemp already has a slicer conneced.
If Not Slicer_Exists(ptTemp, pfTemp, sc) Then Set sc = ActiveWorkbook.SlicerCaches.Add(ptTemp, pfTemp)
End If
ptTemp.ManualUpdate = True
With pfTemp
.Orientation = xlPageField
.EnableMultiplePageItems = False
.CurrentPage = pi.Name
End With
ptTemp.ManualUpdate = False
'Connect slicer on pfTemp to pfOriginal to pass through settings, then disconnect it
sc.PivotTables.AddPivotTable pfOriginal.Parent
If Not bSlicerExists Then
sc.Delete
Else
sc.PivotTables.RemovePivotTable pfTemp.Parent
End If
If bDelete_wksTemp Then
bDisplayAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False
wksTemp.Delete
Application.DisplayAlerts = bDisplayAlerts
ElseIf bDelete_ptTemp Then ptTemp.TableRange2.ClearContents
End If
End If
End With
With Application
.ScreenUpdating = bScreenUpdating
.EnableEvents = bEnableEvents
.Calculation = lCalculation
End With
TimeTaken = Now() - TimeTaken
Debug.Print Now() & vbTab & sRoutine & " took " & Format(TimeTaken, "HH:MM:SS") & " seconds."
End Sub
