Remove (hide) columns from the Pivot Table where a particular columns Grand total is zero

[Ronanm]

Hi

I have spent days attempting to produce some VBA code which tidies up a Pivot Table.

I want to remove (hide) columns from the Pivot Table where a particular columns Grand total is zero. So only show me the columns (these are periods) where there is a total.

My code is as below; It's the PivotTables("MyPivot").PivotFields(i).grand total = 0 bit which isn't right, and the "For Each cell"won't work either to identify the total as it's in a different loop count from the Pivotfield items... .

' first use "find" to select the Grand total cell
  Cells.Find(What:="Grand total", After:=ActiveCell, LookIn:=xlFormulas, _
       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

        'extend it to the PT end
        ActiveSheet.Range(ActiveCell, ActiveCell.End(xlToRight)).Select
       i = 1
       ActiveSheet.Range(ActiveCell, ActiveCell.End(xlToRight)).Select
      'now loop/hide based on cell values.
        For Each cell In Selection
        myval = cell.Value
        ppp = PivotTables("MyPivot").PivotFields(i).Name
        If [B]PivotTables("MyPivot").PivotFields(i).grand total = 0 [/B]And PivotTables("MyPivot").PivotFields(i).Name Like "*/*" Then
        ppp = PivotTables("MyPivot").PivotFields(i).Name
        
                        
                PivotTables("MyPivot").PivotFields("Sum of " & PivotTables("MyPivot").PivotFields(i).Name).Orientation = xlHidden
         
            End If
             i = i + 1
             
             
       Next cell
'Xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Many thanks