[EXCEL] Visible Pivot Items
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
Code:
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.
Code:
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?
Re: [EXCEL] Visible Pivot Items
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
Re: [EXCEL] Visible Pivot Items
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.
vb Code:
'---------------------------------------------------------------------------------------
' 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