Loop through PivotItems: runtime error 91

Question

I have a dataset in a worksheet that can be different every time. I am creating a pivottable from that data, but it is possible that one of the PivotItems is not there. For example:

.PivotItems("Administratie").Visible = False

If that specific value is not in my dataset, the VBA script fails, saying that it can't define the item in the specified Field. (error 1004)

So I thought a loop might work. I have the following:

Dim pvtField As PivotField
Dim pvtItem As PivotItem
Dim pvtItems As PivotItems

For Each pvtItem In pvtField.pvtItems
        pvtItem.Visible = False
Next

But that gives me an 91 error at the For Each pvtItem line:

Object variable or With block variable not set

I thought I declared the variables well enough, but I am most likely missing something obvious...

Answer 1

Try something like this:

Public Function Test()
    On Error GoTo Test_EH

    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim pvtItems As PivotItems

    ' Change "Pivot" to the name of the worksheet that has the pivot table.
    ' Change "PivotTable1" to the name of the pivot table; right-click on the
    ' pivot table, and select Table Options... from the context menu to get the name.
    For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
        Debug.Print "Pivot Field: " & pvtField.Name
        For Each pvtItem In pvtField.VisibleItems
            pvtItem.Visible = False
        Next
    Next

Exit Function

Test_EH:
    Debug.Print pvtItem.Name & " error(" & Err.Number & "): " & Err.Description
    Resume Next

End Function

If you want a function to just test for the existence of a pivot item, you can use something like this:

Public Function PivotItemPresent(sName As String) As Boolean
    On Error GoTo PivotItemPresent_EH

    PivotItemPresent = False

    For Each pvtField In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields
        For Each pvtItem In pvtField.VisibleItems
            If pvtItem.Name = sName Then
                PivotItemPresent = True
                Exit Function
            End If
        Next
    Next

    Exit Function

PivotItemPresent_EH:
    Debug.Print "Error(" & Err.Number & "): " & Err.Description
    Exit Function

End Function

You can call this from your code like this:

If PivotItemPresent("name_of_the_thing") Then
    ' Do something
End If

Answer 2

For Each pvtField In Worksheets("my_sheet").PivotTables("my_table").PivotFields
    For Each pvtItem In pvtField.PivotItems
        Debug.Print vbTab & pvtItem.Name & ".Visible = " & pvtItem.Visible
        /*.PivotItems(pvtItem).Visible = False*/ 
    Next
Next
.PivotItems("certain_Item").Visible = True

That doesn't work still... all the variables are still visible. No error is shown, it compiles yet the values are still there.
The commented line I added there was my own "invention" but it's is not valid.

Edit: Quicky question: Can I use an IF statement to check if a certain PivotItem is actually in the PivotTable Data? Something like

If PivotItem("name_of_the_thing") = present Then {
    do_something()
}

Answer 3

You can't say ".PivotItems(pvtItem).Visible" outside a "With" block. Say "pvtField.PivotItems(pvtItem.Name).Visible = False" instead.

I also edited my original answer to include error handling for when Excel can't set the Visible property. This happens because the Pivot table needs at least one row field, one column field and one data item, so the last of each of these can't be made invisible.

You 'll also get the 1004 errror when trying to access a pivot item that is already invisible; I think you can ignore those.

Answer 4

When I implement the code posted by Patrick, an -

Unable to set the visible property of the PivotItem class

- error is thrown.

Microsoft admits there's a bug: M$ help
But just hiding the line... is not an option ofcourse.

Answer 5

The error is thrown at the end of the loop.
I combined both answers from Patrick into the following:

With ActiveSheet.PivotTables("Table").PivotFields("Field")

    Dim pvtField As Excel.PivotField
    Dim pvtItem As Excel.PivotItem
    Dim pvtItems As Excel.PivotItems

    For Each pvtField In Worksheets("Sheet").PivotTables("Table").PivotFields
        For Each pvtItem In pvtField.PivotItems
            If pvtItem.Name = "ItemTitle" Then
                pvtField.PivotItems("ItemTitle").Visible = True
            Else
                pvtField.PivotItems(pvtItem.Name).Visible = False
            End If
        Next
    Next
End With

If the Item matches a particular string, that Item is set True. Else; Item set False. At the False condition the error is thrown.
I know there is exactly one match for the True condition. Though when I 'F8' my way through the macro, the True condition is never entered...

And that explains the error, everything is set False. (thanks Patrick!)

Leads me to the question... what exactly IS a PivotItem?

Idea of the thing:
It solves (or should solve) the following: a set of Data with a variable size where, for this specific table, one column is of interest. From that column I need the count of a value and have that put in a table. There are some conditions to the table, and a combination with another column is needed as well, so PivotTable is the best solution.
The problem is: in some datasets that one specific value does not appear. The values that DO appear are different every time.

Answer 6

The PivotItems are the individual values in a field (column, row, data). I think of them as the "buckets" that hold all the individual data items you want to aggregate.

Rather than go through all the pivot table fields (column, row, and data), you can just go through the fields you're interested in. For example, this code will show only the specified pivot items for the specified field:

Public Sub ShowInPivot(Field As String, Item As String)
    On Error GoTo ShowInPivot_EH

    Dim pvtField As PivotField
    Dim pvtItem As PivotItem
    Dim pvtItems As PivotItems

    For Each pvtItem In Worksheets("Pivot").PivotTables("PivotTable1").PivotFields(Field).PivotItems
        If pvtItem.Name = Item Then
            pvtItem.Visible = True
        Else
            pvtItem.Visible = False
        End If
    Next

    Exit Sub

ShowInPivot_EH:
    Debug.Print "Error(" & Err.Number & "): " & Err.Description
    Exit Sub

End Sub

Suppose I have a pivot table showing the count of issues per customer release and where they were detected in our SDLC. "Customer" and "Release" are column fields and "Phase" is a row field. If I wanted to limit the pivot table to counting issues for CustomerA, Release 1.2 during QA I could use the sub above like this:

ShowInPivot "Customer", "CustomerA"
ShowInPivot "Release", "1.2"
ShowInPivot "Phase", "QA"

Answer 7

I've got it! :D

Dim Table As PivotTable
Dim FoundCell As Object
Dim All As Range
Dim PvI As PivotItem

    Set All = Worksheets("Analyse").Range("A7:AZ10000")
    Set Table = Worksheets("Analyse").PivotTables("tablename")
    For Each PvI In Table.PivotFields("fieldname").PivotItems
        Set FoundCell = All.Find(PvI.Name)
        If FoundCell <> "itemname" Then
            PvI.Visible = False
        End If
    Next

woohoo

Thanks to MrExcel, the answer was there after all, though deeply buried.

Answer 8

I had same error message too when trying to set pivotitem visible true and false .. this had worked previously, but wasn't working any more ... i was comparing two values, and had not explicitly changed string to integer for comparison .. doing this made error disappear..

.. so if you get this message, check if any values being compared to make the item visible or not are being compared properly .. otherwise pivotitem is null and it can't make that visible or not.

Answer 9

I had an error that said "unable to set the visible property of the pivot item class" at this line:

For Each pi In pt.PivotFields("Fecha").PivotItems If pi.Name = ffan Then pi.Visible = True Else pi.Visible = False '<------------------------ End If Next pi

Then i read on internet that I had to sort manual and then clear the chaché i did that but the error still appeared..... then i read that it was because i had date on that pivot field so i change it firs my colum to general number then the date i wanted to set visible i change it to general number too. then no problem!!!!!!!!!!!!!!.... here it is.... i hope this can be helpfull because i was desperate!!!

Dim an As Variant an = UserForm8.Label1.Caption 'this label contains the date i want to see its the pivot item i want to see of my pivot fiel that is "Date". Dim fan fan = Format(an, "d m yyyy")

Dim ffan ffan = Format(fan, "general number")

Sheets("Datos refrigerante").Activate 'this is the sheet that has the data of the pivottable Dim rango1 As Range Range("B1").Select Range(Selection, Selection.End(xlDown)).Select

Set rango1 = Selection ActiveSheet.Cells(1, 1).Select rango1.Select

Selection.NumberFormat = "General" 'I change the format of the column that has all my dates

'---clear the cache-----

Dim pt As PivotTable Dim ws As Worksheet Dim pc As PivotCache

'change the settings For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Next pt Next ws

'refresh all the pivot caches For Each pc In ActiveWorkbook.PivotCaches On Error Resume Next pc.Refresh Next pc

'-----now select the pivot item i want Dim pi As PivotItem

Set pt = Sheets("TD Refrigerante").PivotTables("PivotTable2")

 'Sets Pivot Table to Manual  Sort so you can manipulate _
 'PivotItems in PivotField
pt.PivotFields("Fecha").AutoSort xlManual, "Fecha"

 ' Speeds up code dramatically
pt.ManualUpdate = True

For Each pi In pt.PivotFields("Fecha").PivotItems
    If pi.Name = ffan Then
        pi.Visible = True
    Else
    pi.Visible = False
    End If
Next pi

pt.ManualUpdate = False
pt.PivotFields("Fecha").AutoSort xlAscending, "Fecha"