CODEWITHSUNDEEP
vbaexcelpivotpivot-table
bart1701
bart1701

Reputation: 65

Pivot: VBA to check the select all but blank in a pivot table

Hi I have a question regarding Pivot tables

With help of the topic below I managed to get a code but it doesn't work, hopefully anybody can see where it goes wrong. How to select (All) in a pivot table filter

Preferably, the VBA should SELECT ALL and, if possible, than unselect the BLANK, but I didn't reach that point yet:

Sub Showallpivot()

Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables("PivotTable1")

For Each pf In pt.PivotFields
    pf.ShowAllItems = True
Next pf
End Sub

What am I doing wrong?

Thanks in advance.

Upvotes: 0

Views: 2926

Answers (1)

OldUgly
OldUgly

Reputation: 2119

The "answer" in the post you linked to, using your code, would look something like this ...

Sub Showallpivot()
Dim pt As PivotTable

    Set pt = ActiveSheet.PivotTables("PivotTable1")
    pt.ClearAllFilters

End Sub

This will remove any filtering that has been put in place, leaving the PivotTable in its full form.

If this doesn't do what you want, then you need to provide a clearer explanation of what the problem is.

Your comment "Preferably, the VBA should SELECT ALL and, if possible, than unselect the BLANK, but I didn't reach that point yet" implies you are looking for other information, beyond showing all of the pivot table. You have not provided enough detail for me to comment further. It should be a separate question.

Upvotes: 1

Related Questions