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

Question

I want to make PivotItem.Visible = False but I keep getting an error:

Unable to set the Visible property of the PivotItem class

I tried all the solutions I found in the internet but none seems to work

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 refreshing the table and this line but still not working:

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Here is a picture 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 attempt to hide all the items on any axis (rows, columns, filters). You can trap this error in your code by comparing the HiddenItems.Count property of your PivotField object to the PivotItems.Count property of the same object and make sure you are not trying to remove the last item of the collection from view:

So in your case statement, you can replace with update 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

Note that whilst manipulating the pivot table Excel won't allow you to remove the last item from an axis - the OK button will become disabled: