CODEWITHSUNDEEP
excelvbaexcel-2010pivot-table
k133
k133

Reputation: 11

VBA code for pivot table select all then deselect 0 and blank

Need help on writing VBA code for selecting all in the pivot table and deselect 0 and blank for several pivot tables in the same sheet. Sometimes the pivot items might not have blank in some pivot tables. I used the recording function, however it does not seem to record the deselecting 0 and blank.

Sheets("DATApivot").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields("AmtIncurred").CurrentPage _
    = "(All)"
With ActiveSheet.PivotTables("PivotTable7").PivotFields("AmtIncurred")
    .PivotItems("0").Visible = True
    .PivotItems("(blank)").Visible = True

End With

Upvotes: 1

Views: 4638

Answers (2)

jeffreyweir
jeffreyweir

Reputation: 4824

Simply put an On Error Resume Next before the filtering part of your code and an On Error Goto 0 afterwards, so that any errors generated by trying to filter non-existent items are simply ignored:

Sub Macro1()

Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("Value")
With pf
    .ClearAllFilters
    On Error Resume Next
        .PivotItems("(blank)").Visible = False
        .PivotItems("0").Visible = False
    On Error GoTo 0
End With
End Sub

Upvotes: 0

QHarr
QHarr

Reputation: 84465

There may be better ways but here is one. Note I am checking that there will always be 1 item left visible before trying to un-check zero and blank.

Option Explicit

Public Sub FilterOutZeroAndBlanks()

    Dim pvt As PivotTable

    Set pvt = ThisWorkbook.Worksheets("DATApivot").PivotTables("PivotTable7")
    Dim pvtField As PivotField
    Set pvtField = pvt.PivotFields("AmtIncurred")

    Dim item As PivotItem
    Dim counter As Long
    Dim targetCounter As Long

    With pvtField

       For Each item In .PivotItems

           If item.Visible Then counter = counter + 1

       Next item

       If .PivotItems("0").Visible And .PivotItems("(blank)").Visible Then
            targetCounter = 2
       ElseIf .PivotItems("0").Visible Or .PivotItems("(blank)").Visible Then
            targetCounter = 1
       End If

       If Not targetCounter = counter Then

           .PivotItems("0").Visible = False
           .PivotItems("(blank)").Visible = False

       End If

    End With

End Sub

Upvotes: 1

Related Questions