Pivot table - help in clearing missing items in Row labels (not column labels)

[Dhamo]

Hi,


I have a Pivot table (PT) and I will clear all missing items in the PT by the below code. But I want to clear only row labels and not the column labels.


Sub DeleteMissingItems2002All()


Dim pt As PivotTable

Dim ws As Worksheet

Dim pc As PivotCache


For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

Next pt

Next ws


For Each pc In ActiveWorkbook.PivotCaches

On Error Resume Next

pc.Refresh

Next pc


End Sub


Please help me in this regard.

 

[Luke M]

Just curious, why do you ask? I'm not sure why you would want extra labels in your PT.

Since the labels are stored in the cache, and not a column/row cache, I'm not sure what you ask can be done. =/

 

[Dhamo]

Hi Luke,


In my pivot table, I have name list (1 to n), tasks and status(always 6). Whenever I run my macro, the Names are getting changed. And missing names also displaying in the refreshed Pivot table.


Initial Pivot table's data range values:

Names : AA, BB, CC, DD, EE

Status: 1*, 2*, 3*, 4*, 5*, 6*

tasks: zero or more per status and per name. (Count should be in Pivot table)


Changed Pivot table's data range values:


Names : A1!, B1, CC, DD

Status: 1*, 2*, 3*, 4*, 5*, 6*

tasks: zero or more per status and per name. (Count should be in Pivot table)


After refreshing the table: (Expected)

Names : A1!, B1, CC, DD (AND NOT AA, BB)

Status: 1*, 2*, 3*, 4*, 5*, 6*

tasks: zero or more per status and per name. (Count should be in Pivot table)


Please suggest me how to explain if you still need a clear picture on my requirement.