How do I delete or remove items from the Hidden Items list that are no longer part of the DataTable. For example if I had a field that was populated with "Apple" and now I no longer have a filed named "Apple" it still appears in the Pivot Hidden Items choices. Is there a way to remove all such items from the pick list?
Welcome to the board
I think you will find that it's not directly possible. The Field name will still show as an option to Show/Hide even after deletion from the source. The only way that I am aware of is to drag the Field completely off the Pivot Table, Refresh then drag it back on.
In Excel 97 you can right click on the name and select Delete, but it only hides it and doesn't really delete.
If i understand your question properly, pls try the following:
If you are talking about a field: Right click on the pivot table then choose Wizard and remove (click on the field and drag it out ) any unwanted fields.
If you are talking about a data in a field without any data then right click on P.Table then choose field then remove the tick mark against "show items with no data".
Hi Dave & Mac,
Thank you for ur reply
I think i have not conveyed the problem properly.
I am attaching the excel file with the data and pivot. I have described the problem also in the first sheet.
Thanks in advance!
I cannot see any description of the problem in the Workbook :no:
I am extremely sorry
I had sent another file wrongly.
Pls. find the correct file here...
The only 2 ways I know to do this are as I suggested in my first post.
Your suggestion was
>The only way that I am aware of is to
>drag the Field completely off the Pivot
>Table, Refresh then drag it back on.
But this does not work. In my sample file, even after dragging the field "Employee" completely off, refreshing and adding the field again in the pivot, the unused values remain.
Pls. try in the excel sheet sent in my previous post. It does not work.
Does it mean there is no other solution :puzzled:
Here is a solution for your problem. But I don’t know how helpful it for you. I had tried this in your Pivot Table and it is working fine.
Create the pivot table as you did in your example and it will display the unwanted fields. Now remove the Employee Field (only) from your pivot and click on refresh data. Only after refreshing your PivotTable insert your Employee Filed back to your PivotTable. (Using Wizard). Now it won’t display your unwanted data.
And any of other valuable members may come with better solutions.
I am not a VBA guru. However, the following code seems to work is your data range does not have the item you are trying to delete.
May be other VBA gods here could help refine this.
.PivotItems("Apple").Visible = False 'This is the item you want to delete
.PivotItems("Apple").Delete 'This syntax seems to delete the unwanted option!
Hope this helps.
Thank you for ur reply. Your solution works