Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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;

    VB:
    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;

    VB:
    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,677

    Re: Count Pivot Fields In Pivot Table

    Have you tried

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

    Web Presence:
    Personal: Datasapien
    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 27th, 2007, 23:56
  2. Pivot Table Fields
    By JMAN in forum EXCEL HELP
    Replies: 14
    Last Post: August 1st, 2006, 03:05
  3. Pivot table fields
    By simoncreighton in forum EXCEL HELP
    Replies: 3
    Last Post: January 15th, 2006, 07:24
  4. Pivot Table hide row fields to...
    By DROM in forum EXCEL HELP
    Replies: 2
    Last Post: November 17th, 2005, 05:10
  5. Replies: 1
    Last Post: September 18th, 2003, 00: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