Using VBA to find filtered Pivot Table column data returns all data in that column

[olofmur]

Hi,

I am trying to iterate over all cells of a specific column in a pivot table, to fetch values of filtered data only. However, it returns all cells in the pivot table column, including the ones that are filtered out. The Pivot Table sits in another sheet than the one I am working with, hence the Worksheet_Activate() function.

Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.Unprotect
    ws.Range("Q7:Q30").ClearContents
    Dim counter As Integer
    
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    counter = 0
    Set pt = ThisWorkbook.Sheets("Pivot").PivotTables("pivotTable")
    
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh
    Set pf = pt.PivotFields("StoreName")
    For Each pi In pf.VisibleItems

        If pi.Visible = True Then
            ws.Cells(7 + counter, "Q").Value = pi.Name
            Debug.Print pi.Name, pi.Visible

        End If
counter = counter + 1
    Next pi
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    ActiveSheet.Protect
End Sub

The Debug.Print shows me that ALL cells in the column have .Visible = True (thus including the ones that are filtered out). I have tried to use pf.PivotItems rather than pf.VisibleItems as well with no luck.

Any ideas?

 

[Comfy]

Of course they will all show True as you are only iterating over pf.VisibleItems and you are testing for pi.Visible = TRUE before doing your Debug.Print


Use this to see items that aren't Visible

For Each pi In pf.PivotItems
Debug.Print pi.Name, pi.Visible
        If pi.Visible = True Then
            ws.Cells(7 + counter, "Q").Value = pi.Name
        End If
counter = counter + 1
    Next pi

You don't need all those checks if iterating through .VisibleItems

For Each pi In pf.VisibleItems
            ws.Cells(7 + counter, "Q").Value = pi.Name
            Debug.Print pi.Name, pi.Visible
counter = counter + 1
    Next pi

 

[olofmur]

Hey,

Thank you for your reply. I forgot to take away the pi.Visible = True when changing to pf.VisibleItems. However, the real problem is that pf.VisibleItems returns things that are not visible! I get all data, including what is hidden by the filter. I'll edit the post for clarity.

EDIT: Could not edit the post?

 

[Comfy]

Have the items been filtered or have the rows been hidden?

When testing with pf.Visible items any items that have been filtered are not included in the iteration.

 

[olofmur]

Have the items been filtered or have the rows been hidden?

When testing with pf.Visible items any items that have been filtered are not included in the iteration.

Hi,

The items have been filtered using slicers. Both pf.VisibleItems and pf.PivotItems with visible=True condition return all items (also those that are not a part of the slice).

Edit: pf.FieldItems to pf.PivotItems

 

[Comfy]

The only thing I can think of is that "StoreName" is not the field that has been filtered.

For Example:

If you have a simple pvt of "StoreID" and "StoreName" and apply a filter to "StoreID" all Store names whether visible to the user or not will return TRUE.

Try iterating through the pivotfield DataRange

Dim r As Range

'...........

For Each r in pf.DataRange
Debug.Print r.Value
Next r

No need to test for Visible

 

[olofmur]

Thank you Comfy! That is indeed how my situation is. I'll make changes when I'm back at the computer and update you on the results.

 

[olofmur]

The only thing I can think of is that "StoreName" is not the field that has been filtered.

For Example:

If you have a simple pvt of "StoreID" and "StoreName" and apply a filter to "StoreID" all Store names whether visible to the user or not will return TRUE.

Try iterating through the pivotfield DataRange

Dim r As Range

'...........

For Each r in pf.DataRange
Debug.Print r.Value
Next r

No need to test for Visible

Dim r As Range

'...........

For Each r in pf.DataRange
Debug.Print r.Value
Next r

This worked perfectly! Thank you for your help and insight on this problem.