Pivot Table ~~Weird~~

R

RalphA

Well-known Member

Joined

May 14, 2003

Messages

3,829

• Sep 12, 2003

• #2

It seems to me that the Old Data is incorporated into your dropdown box BEFORE it is eliminated. Save your Excel file, close it and re-open it. i believe that now the New Data will now be properly loaded into your dropdown box.

As to how to avoid this annoying result totally, somebody else will have to help you.

 

Upvote 0

C

Calicat

Board Regular

Joined

Apr 18, 2003

Messages

144

• Sep 12, 2003

• #3

I asked this question a while ago, and cannot find the thread. Here is the code I used:

'THIS MACRO ELIMINATES PHANTOM DATA
Sub Borrar_PivotItems()


Dim wksH As Worksheet
Dim ptP As PivotTable
Dim pfP As PivotField
Dim piP As PivotItem
Dim i As Integer

On Error Resume Next

For i = 1 To 2
For Each wksH In ActiveWorkbook.Worksheets
For Each ptP In wksH.PivotTables
For Each pfP In ptP.PivotFields
For Each piP In pfP.PivotItems
piP.Delete
Next
Next
ptP.RefreshTable
Next
Next
Next
Set piP = Nothing
Set pfP = Nothing
Set ptP = Nothing
Set wksH = Nothing
End Sub


Calicat

 

Upvote 0

P

Parra

Well-known Member

Joined

Feb 21, 2002

Messages

752

• Sep 16, 2003

• #4

Thanks CaliCat,

It really helped me out.

 

Upvote 0

W

whiskycan

New Member

Joined

Jun 16, 2008

Messages

18

• Nov 18, 2010

• #5

Thanks, this really helped me too. 7 years later!!!

 

Upvote 0

L

Latchmaker

Active Member

Joined

Sep 30, 2005

Messages

308

• Aug 29, 2011

• #6

This is awesome it really works well!!

 

Upvote 0

xenou

MrExcel MVP

Joined

Mar 2, 2007

Messages

16,836

Office Version

1. 2019

Platform

1. Windows

• Aug 29, 2011

• #7

Note that a solution without code that usually works is to remove everything from the PT and refresh the empty pivot table, then add the items back (often you only need to remove the field that has the old data which is now missing).

See:
http://www.contextures.com/xlpivot04.html

 

Upvote 0

J

jahli

Board Regular

Joined

Oct 2, 2006

Messages

121

• Apr 27, 2012

• #8

Calicat - This worked like a charm. Thank you so much!!!

 

Upvote 0

J

jahli

Board Regular

Joined

Oct 2, 2006

Messages

121

• Jun 6, 2012

• #9

More help... This macro worked wonderfully on several pivot tables and table fields but doesn't seem to be able to clean up the following month info where Excel thinks there was a version 1 of the month so it numbers some as 2 i.e. Jun2, Oct2, etc.

Year Month Total
2011 Jun2 154
Jul2 392
Aug2 260
Sep2 222
Oct2 261
Nov 296
Dec2 267
2012 Jan 230
Feb 176
Mar 196
Apr 226
May 239

 

Upvote 0

J

jahli

Board Regular

Joined

Oct 2, 2006

Messages

121

• Jun 6, 2012

• #10

xenou said:

Note that a solution without code that usually works is to remove everything from the PT and refresh the empty pivot table, then add the items back (often you only need to remove the field that has the old data which is now missing).

See:
http://www.contextures.com/xlpivot04.html

Click to expand...

Actually, this does not work. Have done that and nada...

 

Upvote 0