Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Count Pivot Fields In Pivot Table

  1. #1
    Join Date
    29th December 2004
    Posts
    61

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,680

    Re: Count Pivot Fields In Pivot Table

    Have you tried

    [vba]For Count = 1 To pt.PivotFields.Count[/vba]
    Kind Regards, Will Riley

    LinkedIn: Will Riley

  3. #3
    Join Date
    29th December 2004
    Posts
    61

    Re: Count Pivot Fields In Pivot Table

    Thanks Will.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Pivot Table Fields Into Tabs
    By xlnewbie in forum EXCEL HELP
    Replies: 2
    Last Post: June 28th, 2007, 00:56
  2. Pivot Table Fields
    By JMAN in forum EXCEL HELP
    Replies: 14
    Last Post: August 1st, 2006, 04:05
  3. Pivot table fields
    By simoncreighton in forum EXCEL HELP
    Replies: 3
    Last Post: January 15th, 2006, 08:24
  4. Pivot Table hide row fields to...
    By DROM in forum EXCEL HELP
    Replies: 2
    Last Post: November 17th, 2005, 06:10
  5. Replies: 1
    Last Post: September 18th, 2003, 01:13

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno