How to Iterate through the pivot items in Pivot Field inside Report Filter RRS feed

  • Question

  • I used the below code to print the Pivot Items present in Each PivotField and their visible status. I have 5 Pivot Fields in the Report Filter. 

    The problem is, it is fetching the no.of Pivot Fields Properly. But when it comes to the no.of Pivot Items is returning 0 for every pivot field. In real each Field has many items. I'm not trying to print the visible items. But Items and their Visible Status.


    Dim pf As PivotField
    Dim pt As PivotTable
    Dim pi As PivotItem

    Set pt = Sheets("Reasons").PivotTables("PivotFields")

    For Each pf In pt.PageFields For Each pi In pf.PivotItems Debug.Print pi.Name & " : " & pi.Visible Next Next


    Wednesday, August 3, 2016 3:52 PM

All replies

  • Hello,

    I'm not sure what you are trying to do.

    The code you posted works for me.

    To get the number of pivotItems: 

    Dim pf As PivotField
    Dim pt As PivotTable
    Dim pi As PivotItem
    Set pt = Sheets(2).PivotTables(1)
    For Each pf In pt.PageFields
        Debug.Print pf.PivotItems.Count

    However using pf.VisibleItems does not satify me, thats why I always write code to count them (like you did)

    Hope it helps?

    Wednesday, August 10, 2016 11:03 AM
  • Have you tried with below...

    For Each pf In pt.PivotFields

    It is better to loop over general fields and then you can check if it is in row, data or Page by "Orientation" property.

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 12, 2016 8:28 AM