Hide Pivot Table Filter

Question

I was trying to hide filter on pivot table. There is no easy way to toggle it in Pivot table options.

I found this VBA code to hide the filter

Sub removepivotfilter()

Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            For Each pf In pt.PivotFields
            pf.EnableItemSelection = False
              Next pf
        Next pt
    Next ws
               
End Sub

I added an if condition to toggle it.

For Each pf In pt.PivotFields
    If pf.EnableItemSelection = True Then
       pf.EnableItemSelection = False
       ElseIf pf.EnableItemSelection = False Then
       pf.EnableItemSelection = True
    End If
Next pf

My question is. I want to avoid For each and make it a one liner code. But I don't see Intellisense pop up when I type ws.PivotTables(1) how do I know the properties and methods of PivotTable. I can read documentation but cannot be sure without Intellisense support in VBE.

Dim pt As PivotTable 

Dim pf As PivotField 

Dim ws As Worksheet 

Dim aWB As Workbook 

Set aWB = ThisWorkbook 

Set ws = aWB.Worksheets("Sheet1") 

ws.PivotTables(1).

Answer 1

You could use:

            For Each pf In ThisWorkbook.Worksheets("Sheet1").PivotTables(1).PivotFields
               pf.EnableItemSelection = Not pf.EnableItemSelection
            Next pf

Answer 2

Yes, Sheet1 not present earlier.

Not pf.EnableItemSelection will turn off filter, does it for all other columns when it goes out of Next, filter is on.

How does it turn it back again?

Answer 3

Then the code workbook doesn't have a sheet called Sheet1 in it.

Answer 4

But I don't see Intellisense pop up when I type ws.PivotTables(1)

The Intellisense is only available for typed objects, e.g. Pt in here:

But the PivotTables function returns only un-typed objects:

An "object" has no members, therefore no Intellisense.

[quote}

My question is. I want to avoid For each and make it a one liner code.

[/quote}

My counter question is: Why?

(Makes no sense, you can put all your code into a sub and pass your Pt as argument...)

Andreas.

Sub RemovePivotfilters()
Dim Ws As Worksheet
Dim Pt As PivotTable
For Each Ws In Worksheets
For Each Pt In Ws.PivotTables
TogglePivotfilter Pt, False
Next
Next
End Sub

Sub TogglePivotfilter(ByRef Pt As PivotTable, ByVal Enable As Boolean)
Dim Pf As PivotField
For Each Pf In Pt.PivotFields
Pf.EnableItemSelection = Enable
Next
End Sub

Answer 5

I get RTE 9 at For each

pf = Nothing