VBA shortcut to control Pivot Table

**LB79** · 01-12-2011, 12:45 PM

Hi all,

Please can someone tell me if there is a way to uncheck all items in a pivot table row label selection without listing each item (There are a lot of items).
Ive been using the below code to remove my blanks from my reports but I need now to uncheck everything (then check on a single item).
Hope that makes sense

Thanks

With ActiveSheet.PivotTables("PMA4").PivotFields("Rep Month")
.PivotItems("(blank)").Visible = False
End With

**Andy Pope** · 01-13-2011, 06:20 AM

You have to loop through the items.
http://www.contextures.com/xlPivot03.html#ShowItem

this will display only (blank) item. There is not error trapping to handle all items being made invisible, see example in above link for more info

    Dim ptItem As PivotItem
    With ActiveSheet.PivotTables("PMA4").PivotFields("Rep Month")
        For Each ptItem In ptField.PivotItems
            ptItem.Visible = ptItem.Value = "(blank)"
        Next
    End With

VBA shortcut to control Pivot Table

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA shortcut to control Pivot Table

Re: VBA shortcut to control Pivot Table

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions