Pivot Table - Calc Item Problem

[Guest]

Hello,

I'm trying to insert a quarterly subtotal (Jan+Feb+Mar) in my pivot table.
The problem I'm having is that when I insert the calculated item, my pivot
table creates a bunch of blank rows when I only want subtotals for fields
that have data.

Example:
Company Region Jan Sales Feb Sales Mar Sales
MSFT South 10 10 10
HPQ North 15 15 15

Here's the output I'm trying to get with "Q1" my calc item:
Company Region Jan Sales Feb Sales Mar Sales Q1
MSFT South 10 10 10 30
HPQ North 15 15 15 45

Here's the output I'm getting with unwanted zero/blank cells:
Company Region Jan Sales Feb Sales Mar Sales Q1
MSFT South 10 10 10 30
North 0 0 0 0
HPQ South 0 0 0 0
North 15 15 15 45

How do I only get quarterly subtotals for rows that have data? Another way
to put it, how do I get rid of all the rows with zeros?

Thanks in advance.

 

[alisonb]

I've got the same problem as Scott. Lots of rows with unwanted zeros
when calculated item inserted. Has anyone found a way of getting rid of
them?

alison

 

[Debra Dalgleish]

You can use programming to hide the rows with a zero total. For example:

'======================================
Sub HidePivotZeroRows()
'hide worksheet rows that contain all zeros
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rng) = 0 Then
rng.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
rng.EntireRow.Hidden = False
End If
Next rng
End Sub

'================================

Sub UnhidePivotRows()
'unhide all rows
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
rng.EntireRow.Hidden = False
Next rng
End Sub
'====================================