[EXCEL] Visible Pivot Items

[agmorgan]

If I create a Pivot Table with 3 sets of row items but hide all but 2 of the top level pivot items, the visible property of the 3rd level items doesn't seem to be affected.
e.g. This shows the name and summary at level 1

    For Each pi In pt.RowFields(1).PivotItems
        If pi.Visible = True Then
            Debug.Print pi.Name
            Debug.Print pi.DataRange
        End If
    Next

However, this doesn't show the corresponding name and summary at level 3 (as shown on screen).
It shows everything at level 3 regardless of whether it is hidden by level 1 or not.

    For Each pi In pt.RowFields(3).PivotItems
        If pi.Visible = True Then
            Debug.Print pi.Name
            Debug.Print pi.DataRange
        End If
    Next

How can I loop over the pivot table just getting the displayed info at level 3?

[Davadvice]

are you wishing to display the items at level 3 or just read the data for another purpose ?

if you are just reading the items you may just have to loop through using a FOR or DO WHILE?

if you are wishing to control the visual appearance then that may be more difficult.

do you have an exaple you may get more help if people can see what you are needing to do.

thanks
David

[agmorgan]

I only wanted to read the items.
e.g. I might have 100 items, but after filtering at level 1, 30 are displayed on screen.
I wanted to read all the visible items at level 3 (i.e. 30 items).
The code in the first post outputs 100 items even though it is checking If pi.Visible = True

To get around this I had to write my own isVisible method.
Basically the code will throw an error if it isn't one of the 30 as DataRange will be null.
This error is then caught and ignored returning false.

'---------------------------------------------------------------------------------------
' Procedure : isVisible
' Author    : agmorgan
' Date      : 12/08/2009
' Purpose   : Check if the PivotItem is not hidden by filtering at a higher level
'---------------------------------------------------------------------------------------
Public Function isVisible(pi As PivotItem) As Boolean
 
    On Error GoTo isVisible_Error
    Dim strAddress As String
    strAddress = pi.DataRange.Address
    isVisible = True
 
    On Error GoTo 0
    Exit Function
 
isVisible_Error:
    isVisible = False
End Function