VBA for finding filters and hidden items in a pivot column

macfuller

Active Member
Joined
Apr 30, 2014
Messages
297
Office Version
  1. 365
Platform
  1. Windows
I am trying to identify VBA code to tell me if pivot table columns have a filter on them, either value/label or by selected items. It's soooo easy to forget you set a filter, there's a tiny icon change on the column that could be a sort, could be a filter, so I just want something to tell me if I've left the engine running.

It's easy enough to hide something if it's visible, but to find something that's already hidden is tough.

In the image below I have unchecked two of the values in the list. So, I would like to know that the column has been filtered.

1611694469790.png


This is a Power Pivot table from the data model. It seems the PivotFilters don't enumerate so you have to reference the filter directly. The code below works to see if there is a value or label pivot filter on the selected field using ptFld.PivotFilters(1).FilterType

PivotItems can enumerate but they only show what's visible, so I can't check each item's .Visible property for xlHidden since only the visible show up! I tried the ptFld.HiddenItemsList property but this always returns a null string.

How can I tell that I'm missing Custom Interfaces and EDI from my pivot table results? I don't even need the strings listed, I'll just take a Boolean value that not everything is showing...

VBA Code:
    Dim pt As PivotTable
    Dim ptCube As CubeField
    Dim ptFld As PivotField
    Dim ptFltr As PivotFilter
    Dim ptItem As PivotItem
   
    Dim iFiltered As Integer
   
    Dim arrV As Variant
   
    Dim dict As Dictionary
    Set dict = Dictionary_Create_PivotFilterType()
   
    Dim strFilters As String
    strFilters$ = vbNullString

    For Each pt In ActiveSheet.PivotTables
        For Each ptFld In pt.PivotFields
'   PageField values are in the top filter section
'       Decide if you want to include those in the list

'   PivotItems lists selected values whether or not they're filtered - however many show
'   PivotFilters do not appear to enumerate - you have to reference directly

'   Is it filtered?
            On Error Resume Next
            If ptFld.name <> "Values" Then
                Select Case ptFld.Orientation
                    Case xlHidden, xlPageField
                    Case Else
'   This code checks to see if a value or label filter is applied, but it doesn't work for selecting values from a checkbox
                        iFiltered% = 0
                        iFiltered% = ptFld.PivotFilters(1).FilterType
                        If iFiltered% > 0 Then
                            If strFilters$ = vbNullString Then
                                strFilters$ = pt.name & " || " & ptFld.name & ": " & Dictionary_RetrieveValue(dict, iFiltered%)
                            Else
                                strFilters$ = strFilters$ & vbLf & pt.name & " || " & ptFld.name & ": " & Dictionary_RetrieveValue(dict, iFiltered%)
                            End If
'                            Debug.Print ptFld.name & ": " & Dictionary_RetrieveValue(dict, iFiltered%)
                        End If

'   Now we need to look at each pivotitem to see if it's hidden.
'       again, the enumeration only appears to go through visible items

                        arrV = ptFld.HiddenItemsList

'  This always returns a null string

                End Select
            End If
        Next ptFld
    Next pt
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

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