+ Reply to Thread
Results 1 to 9 of 9

Pivot Table - Calc Item Problem

  1. #1
    Scott
    Guest

    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?

    Thanks in advance.


  2. #2
    Registered User
    Join Date
    06-01-2005
    Posts
    1

    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. #3
    Debra Dalgleish
    Guest

    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. #4
    Debra Dalgleish
    Guest

    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. #5
    Debra Dalgleish
    Guest

    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. #6
    Debra Dalgleish
    Guest

    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. #7
    Debra Dalgleish
    Guest

    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. #8
    Debra Dalgleish
    Guest

    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. #9
    Debra Dalgleish
    Guest

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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