Pivot table - hide zero values

[towners]

Hello all,

I have several pivot charts and want to hide zero values.

The data is automatically collected from an external data source and I want to automate the pivot chart refresh etc.

Each pivot chart displays values from a number of columns of data, I only want to chart the data when any one of the columns has a value so I have included a simple "Sum" column that sums up the values of my data columns and have included this in my pivot table field list within the "Report Filter" section. Thus allowing me to deselect "0" values.

The problem is that when my data is refreshed any new values in the pivot table are not included because of the filter that is applied by deselecting "0". So currently I have to manually select all from the pivot table report filter and then deselect "0".

I want to perform this programatically so recorded the macro, here is the result.

    ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum")
        .PivotItems("14").Visible = True
        .PivotItems("15").Visible = True
    End With
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum"). _
        EnableMultiplePageItems = True
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PivotTable4").PivotFields("Sum")
        .PivotItems("0").Visible = False
    End With

My problem is the ".PivotItems" lines. Because the data is dynamic I can't know what new values need to be set to Visible and this is preventing me from programatically resetting the filter.

Can anyone help me with some code to refresh the pivot tables and reapply the filter to remove "0" values?

Thank you

Towners