Announcement

Collapse
No announcement yet.

Count Pivot Fields In Pivot Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Count Pivot Fields In Pivot Table

    I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

    Code:
    Sub RemoveGhostPivotItems()
    
        Dim ghost As PivotItem
        Dim pt As PivotTable
        
        Set pt = ActiveSheet.PivotTables(1)
    
        pt.ManualUpdate = True
    
        For Count = 1 To 10
            On Error Resume Next
            For Each ghost In pt.PivotFields(Count).PivotItems
                ghost.Delete
            Next ghost
        Next Count
    
        pt.ManualUpdate = False
        
    End Sub
    My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient.
    In otherwords;

    Code:
    For Count = 1 To Total_Number_of_PivotFields
            On Error Resume Next
            For Each ghost In pt.PivotFields(Count).PivotItems
                ghost.Delete
            Next ghost
        Next Count
    Also, I don't know whether I need to turn on the ManualUpdate to speed up the code but I thought I would try it.

  • #2
    Re: Count Pivot Fields In Pivot Table

    Have you tried

    For Count = 1 To pt.PivotFields.Count
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Re: Count Pivot Fields In Pivot Table

      Thanks Will.

      Comment

      Working...
      X