Pivot Table Item List Corrupt

Topic

New Reply

In my Excel 97 pivot tables, the item list for my Page items sometimes becomes corrupted. For example, when an entry in the item dropdown box is selected, the data that appears in the table is really for a different entry. This has happened several times, and the only way I have found to fix it is to rebuild the pivot table. Has anyone else encountered this? Also, does anyone have any info on how to avoid this, or how to rebuild the “internal list index” for the item? Since this seems to happen at random, I am unable to trust the resulting output of the pivot table without verifying the output.

Also a related topic: When data records are removed from the data source for pivot table, and a value is completely removed from a column, the value still appears in the item entry list in the pivot table. Again, a way to rebuild the item entry list would help. Refreshing the pivot table will not fix this.

Reply | Quote

Replies

Steve,
Do you have these problems with all your Pivot Tables or is just a specific problem case.?
Would it be possible to upload a sample of a table with these problems, as without knowing the nature of the underlying data it is difficult to surmise the cause of the problem.

Andrew C

Reply | Quote

Andrew,

Sorry about not responding sooner. I was trying different techniques and needed to get a working solution for last month’s processing, and I then expanded the use of these techniques.

Through testing I discovered a few behavioral quirks of pivot tables that were causing my problems. These seem to occur more in Excel 97 than 2000.

First, I found that the “index” for a page item in a pivot table can become corrupted if VBA code is used to select an entry that is, in fact, not in the list. The selected value takes the place of another entry in the list! This may be hard to duplicate, but I know it happens. To avoid this, I always select the “(All)” entry before choosing a specific entry. I also added records to my source data that include all the entries that I think should exist in the data. One would expect to get zeros if I select an invalid value, but it doesn’t seem to work that way. The combination of these seems to avoid the problem. I know this was necessary in XL97, but I believe that part of this problem (but not all) is fixed in XL2000. More testing is needed, but I skipped it and upgraded our systems to XL2000.

There does not seem to be a way to rebuild the index for an item list. Old entries for data values that no longer exist in the source data will continue to appear in the item list. However, it seems that this sometimes can be fixed by changing the source data before refreshing the pivot table for the first time after opening the workbook. This doesn’t always work, so we always check the item list and hide the entries we don’t want. (I want 12 months of data, so I always hide the oldest entry each month.) The only way I have found to fix this is to rebuild the pivot table from scratch (which can be made easier with code).

One other interesting thing I found. If the currently selected page items cause data to be returned, then a new entry is selected which returns no data, the pivot table is sometimes not set to zeros or blanks–it retains the data from the previous page item! This seems to happen only in XL97. I avoid this by first selecting a dummy item entry that has zeros in all data fields, which will return at least one zero in the pivot table. Again, this shouldn’t be required, but I found that it is…

Thanks for responding to my post. I hope this helps someone else someday who runs across the same problems. I don’t expect MS to have this fixed in XP (although they should), but maybe someone at MS will read this and look at fixing it for Office 2003.

Reply | Quote

I found (I don’t remember where) the following code to delete unused pivot items. It appears to work.

Sub deloldpivotitems()
Set oPiv = ActiveSheet.PivotTables(1)

For Each oField In oPiv.PivotFields
If oField.Name “Data” Then
For Each oItem In oField.PivotItems
If oItem.RecordCount = 0 And Not oItem.IsCalculated Then oItem.Delete
Next
End If
Next

End Sub

Reply | Quote

Thanks! I tried this and it worked great. I don’t know how I overlooked the RecordCount property, as I have been through all the pivot table help screens many times.

A few other notes on using this for anyone else: The “(All)” item is not actually an item, so don’t worry about accidentally deleting it. If you want to keep the possibility for blank entries, be sure to exclude items with a Name equal to “(blank)”. (If there are blank records without data, the record count will be zero for these.) Finally, you can limit the search to the Page, Data, Column, or Row fields collections, or even to only one field.

Reply | Quote

Hi…digging up an old post, here, but very relevant to what we need. This macro seems to work well in most instances, but for one of our pivot tables, it comes back with an application defined or object defined error. When I click on debug, this is highlighted:

oItem.Delete

this is the line right after the Then and before the ending statement of

Next
End If
Next

End Sub

The problem pivot table has calculated fields in it. We suspect that may be the reason behind the error, but the macro has worked on other pivot tables with calculated fields, so we’re confused…

Does anyone know what may be causing this? Is there any way to remove the data manually or by some other method? We are on Excel 97, SR2. The problem also occurs on more than one computer, so it shouldn’t be anything to do with that…

Thanks!

– Brett

Reply | Quote

OK. I discovered that the problem we were running into had two parts. The error came up as a result of blank rows being included in the pivot table source data (data was contained in A1:F132, but pivot table source was A1:F200 to allow for future growth of the dataset). We can live with this limitation, it’s not a big deal.

The real problem comes up when running this macro on a pivot table that contains groups. We often group our data to make a report more summarized (a common example would be grouping Jan, Feb, Mar into Q1, etc). Is there a way to tweak this code to be able to handle grouped data? One of these posts stated that the macro could be run on only a certain section (page, row, column) of the pivot table. How would I go about telling the macro to do this?

Any help would be greatly appreciated.

Thanks!

– Brett

Reply | Quote