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

olofmur

New Member
Joined
May 27, 2016
Messages
21
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.

Code:
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?
 
Last edited:

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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
Code:
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

Code:
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

New Member
Joined
May 27, 2016
Messages
21
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?
 
Last edited:

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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

New Member
Joined
May 27, 2016
Messages
21

ADVERTISEMENT

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
 
Last edited:

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,379
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

Code:
Dim r As Range

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

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

No need to test for Visible
 

olofmur

New Member
Joined
May 27, 2016
Messages
21
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

New Member
Joined
May 27, 2016
Messages
21
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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,310
Messages
5,600,879
Members
414,413
Latest member
Sinbin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top