If I have a simple data table like:

NAME AGE
A 10
B 20

If I then create a pivottable, named myTable, that looks into the
above, which has the Name field up above the pivottable as a dropdown.
If I then run the following:

MsgBox
ThisWorkbook.ActiveSheet.PivotTables("myTable").Pi votFields("Name").PivotItems.Count

....it will return 3, representing A,B and All.
If I then change the table to :

NAME AGE
A 10
B 20
C 30
D 40

....the message box will return 5, representing A,B,C,D and All.
The problem occurs when I change the data table to:

NAME AGE
A 10
X 20

....the message box now returns 6, which I believe must represent A, B,
C, D, X and All. Surely this is a bug as the items B, C, and D should
not be included !!

Any comments or ways of avoiding this would be much appreciated.
Regards
Jason.

I'm not sure, but maybe you need to adjust the pivot table's range.

Regards,
Steve
www.swiss-ins.com

WhytheQ wrote:
If I have a simple data table like:

NAME AGE
A 10
B 20

If I then create a pivottable, named myTable, that looks into the
above, which has the Name field up above the pivottable as a dropdown.
If I then run the following:

MsgBox
ThisWorkbook.ActiveSheet.PivotTables("myTable").Pi votFields("Name").PivotItems.Count

...it will return 3, representing A,B and All.
If I then change the table to :

NAME AGE
A 10
B 20
C 30
D 40

...the message box will return 5, representing A,B,C,D and All.
The problem occurs when I change the data table to:

NAME AGE
A 10
X 20

...the message box now returns 6, which I believe must represent A, B,
C, D, X and All. Surely this is a bug as the items B, C, and D should
not be included !!

Any comments or ways of avoiding this would be much appreciated.
Regards
Jason.

Hi

Take a look at the information on Debra Dalgleish's site regarding PT's
retaining old items.
Debra also shows some code there for "cleaning" the list
http://www.contextures.com/xlPivot04.html

--
Regards

Roger Govier


"WhytheQ" wrote in message
ps.com...
If I have a simple data table like:

NAME AGE
A 10
B 20

If I then create a pivottable, named myTable, that looks into the
above, which has the Name field up above the pivottable as a dropdown.
If I then run the following:

MsgBox
ThisWorkbook.ActiveSheet.PivotTables("myTable").Pi votFields("Name").PivotItems.Count

...it will return 3, representing A,B and All.
If I then change the table to :

NAME AGE
A 10
B 20
C 30
D 40

...the message box will return 5, representing A,B,C,D and All.
The problem occurs when I change the data table to:

NAME AGE
A 10
X 20

...the message box now returns 6, which I believe must represent A, B,
C, D, X and All. Surely this is a bug as the items B, C, and D should
not be included !!

Any comments or ways of avoiding this would be much appreciated.
Regards
Jason.