# Pivot Table - Calc Item Problem

1. ## Pivot Table - Calc Item Problem

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?

2. ## problem with pvito table calculated item

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

3. ## Re: Pivot Table - Calc Item Problem

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
'====================================

alisonb wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

4. ## Re: Pivot Table - Calc Item Problem

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
'====================================

alisonb wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

5. ## Re: Pivot Table - Calc Item Problem

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
'====================================

alisonb wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

6. ## Re: Pivot Table - Calc Item Problem

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
'====================================

alisonb wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

7. ## Re: Pivot Table - Calc Item Problem

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
'====================================

alisonb wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

8. ## Re: Pivot Table - Calc Item Problem

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
'====================================

alisonb wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

9. ## Re: Pivot Table - Calc Item Problem

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
'====================================

alisonb wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1