Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I am trying to create a Pivot Table that I can hide the totals or not have them created . I am annexing a pivot table workbook. a w/s with the database and a w/s with a pivot table the blue highted totals are the rows I want to hide or not create.

    Anyone with suggestions??

    Thanks
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could try this PivotTableUpdate event. Right click on the tab for the sheet that contains the PT and choose View Code. Paste the following:
    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pRange As Range, i As Long
    Application.ScreenUpdating = False
    Set pRange = Target.RowRange
    Target.RowRange.Rows.Hidden = False
    For i = pRange.Row To pRange.Rows.Count + pRange.Row - 2
        If InStr(Cells(i, pRange.Column), "Total") > 1 Then
            Cells(i, pRange.Column).EntireRow.Hidden = True
        End If
    Next
    
    Application.ScreenUpdating = True
    
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I really do not know macros or VBA. Is there a way to hide the rows "total" rows requested.

    Thank you

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Just RIGHT Mouse Click over any of the BLUE Rows and Choose HIDE off the Menu

    See Attached where they are hidden.

    [attachment=88187:PIVOT TABLE REPORT HIDING LINE ITEMS HIDDEN TOTALS.xls]

    [attachment=88190:PivotHiddenTotals.jpg]
    Attached Images Attached Images
    Attached Files Attached Files
    Andrew

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Ohio, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    You could try this PivotTableUpdate event. Right click on the tab for the sheet that contains the PT and choose View Code. Paste the following:
    Code:
    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim pRange As Range, i As Long
    Application.ScreenUpdating = False
    Set pRange = Target.RowRange
    Target.RowRange.Rows.Hidden = False
    For i = pRange.Row To pRange.Rows.Count + pRange.Row - 2
        If InStr(Cells(i, pRange.Column), "Total") > 1 Then
            Cells(i, pRange.Column).EntireRow.Hidden = True
        End If
    Next
    
    Application.ScreenUpdating = True
    
    End Sub

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Ohio, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    After pasting the code how does one get it to execute? It doesn't show up as a macro. Trying to learn VBA.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It is event code and runs automatically when you refresh the pivot table (assuming you put the code in the worksheet's code module, not a normal module)
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Right click one of the total lines.
    Click on Field Settings
    Click None.

  10. #10
    New Lounger
    Join Date
    May 2010
    Location
    Rocky Mount, NC
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    Well this is not pretty but it works.

    Select entire row with total
    Right click on the row
    Select "Hide"

  11. #11
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello All - Another simple alternative is to double click the word "State" in cell A4 and choose None for Subtotals.

    For appearances, could also consider Formatting each of the choices to be comma with 2 decimals.
    Click somewhere in the Pivot Table and select the Pivot Table toolbar...
    choose Pivot Table Wizard>Layout>double click Sum of Total Hours>Number>Number>2 decimals>Use 1000 separator>OK.

    Do the same for Sum of Total Amount.

    Note: Can double click on any number within the Pivot Table to see the supporting data for that cell

    Tim

Posting Permissions

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