Cannot set the Visible property of the PivotItem class when setting PivotItem.Visible = false

Question

I want to do PivotItem.Visible = False , but I still get the error message:

Unable to set the Visible property of the PivotItem class

I tried all the solutions that I found on the Internet but no one works

 Sub FloorCompareSetter() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim PivotSheet As Worksheet Set PivotSheet = ThisWorkbook.Worksheets("PIVOT") PivotSheet.PivotTables("PivotTable5").RefreshTable Set pt = PivotSheet.PivotTables("PivotTable5") pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Set pf = pt.PivotFields("Period") For Each pi In _ pt.PivotFields("Period").PivotItems Select Case pi.Name Case Is = "1601A" pi.Visible = True Case Else pi.Visible = False 'error End Select Next pi End Sub 

I tried updating the table and this row, but still did not work:

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Here is an image of my pivot table:

What am I doing wrong and how can I solve this problem?

Answer 1

You will get this error if you try to hide all elements on any axis (rows, columns, filters). You can lure this error into your code by comparing the HiddenItems.Count property of your HiddenItems.Count object with the PivotField property of the same object and make sure that you are not trying to remove the last element of the collection from the view:

So, in your case statement, you can replace with something like:

 Select Case pi.Name Case Is = "1601A" pi.Visible = True Case Else If pf.HiddenItems.Count < (pf.PivotItems.Count - 1) Then pi.Visible = False Else MsgBox "Cannot hide all the items on this axis" Exit For '<~~ break the loop to stop the MsgBox popping up End If End Select 

Please note that when manipulating the Excel pivot table, the last element from the axis will not be deleted - the OK button will become unavailable:

Answer 2

I added

On Continuation of error Next

before

pi.Visible = False 'error

now it works!