none
Hiding a Sum Value in a Pivot Table RRS feed

  • Question

  • I have a pivot table where I do not want a sum value to appear.   I found some code but not able to adjust to meet my needs.

    I do not want the Sum of the Shift Rate Tot. appearing.  This appears throughout the pivot table depending on my results.

    Here are two examples of code I tried to adjust:  The worksheet is named Signature, pivot table is pivotTable1.

    Dim PT As PivotTable
    'Set pt = Worksheets(Signature).PivotTables("pivotTable1")
    
    'pt.PivotFields("Data").Orientation = xlHidden
    PT.DataPivotField.Orientation = xlHidden
    
     Set PF = PT.PivotFields("Sum of Shift Rate Tot.")
         
         PF.Orientation = XlPivotFieldOrientation.xlDataField
         PF.Function = xlSum
         PF.Caption = "Rate Total"
         
    End Sub
    
    
    Sub HidPField()
    
        Dim PT As PivotTable, PF As PivotField
        
        Set PT = ActiveSheet.PivotTables("PivotTable1")
        
        For Each PF In PT.DataFields
            If PF.Position = 4 Then
                PF.Orientation = xlHidden
                Exit Sub
            End If
        Next PF
    End Sub

    Saturday, May 6, 2017 1:49 PM

Answers

  • Hello Jean,

    I am glad that you resolved the issue. I suggest you mark it as answer to close the thread. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JAMHOME Friday, May 12, 2017 9:59 PM
    Friday, May 12, 2017 2:10 AM
    Moderator

All replies

  • Hello,

    PivotField.Orientation could help you hide fields.
    Here is the example.

    Dim PT As PivotTable
    Set PT = Sheets("Sheet3").PivotTables(1)
    Dim PF As PivotField
    Set PF = PT.PivotFields("Sum of Price")
    PF.Orientation = xlHidden

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 8, 2017 1:54 AM
    Moderator
  • Hi Celeste

    Need more help with this.  I altered accordingly:

    Sub HideTitle()
    Dim PT As PivotTable
    Set PT = Sheets("Signature").PivotTables(1)
    Dim PF As PivotField
    Set PF = PT.PivotFields("Sum of Shift Rate Tot.")
    PF.Orientation = xlHidden
    End Sub
    

    It deletes the entire column--I only want to delete the title.

    Jean

    Monday, May 8, 2017 3:50 PM
  • Hi Celeste Again

    To be clearer--I want to delete or hide the caption "Sum of Shift Rate Tot." and the sum amounts that get generated throughout the pivot table.  

    Here is a better example: Jean

    Monday, May 8, 2017 3:57 PM
  • Hello,

    We are unable to delete or hide a single cell in a pivot table, to work around, we could change its caption into emtpy using PivotField.Caption

    PF.Caption = ""

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, May 9, 2017 7:27 AM
    Moderator
  • Hi

    I found the manual steps to do this--here they are:

    Home, Conditional Formatting, New Rule, All cells ..., Use formula ..., True, Format, Custom, ;;;, OK, OK

    I need to automate this process with a macro--here is the macro code:

    Sub DelSumRate()
    '' DelSumRate Macro
    '    Sheets("Signature").Select
        Range("D25").Select
        Selection.NumberFormat = ";;;"
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        ExecuteExcel4Macro "(2,1,"";;;"")"
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions(1).ScopeType = xlFieldsScope
    End Sub

    It bombs at the following line:

     ExecuteExcel4Macro "(2,1,"";;;"")"

    Jean

    Wednesday, May 10, 2017 9:35 PM
  • Hi JAMHOME,

    Is setting PF.Caption not working for you? Did you get any error when setting Caption? Range.NumberFormat returns Null if all cells in the specified range don't have the same number format. So the cell text is hidden (actual value is null) when you set the cell numberformat to ";;;".This could also be an workaround. You could comment out other code to make the sub run. Just retain:

    Sub DelSumRate()
        Range("D25").Select
        Selection.NumberFormat = ";;;"
    End Sub

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 11, 2017 10:09 AM
    Moderator
  • Hi

    I tried both suggestions and they delete the entire column.  

    I found this code on Microsoft:

    Sub format()
        With ActiveSheet
            .Columns("L:L").NumberFormat = "General"
            With Range("L2:L500")
                .FormatConditions.Delete   ' remove all CF rules from L2:L500
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$A2=""Trunk"""
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
                .FormatConditions(1).NumberFormat = "[h]:mm:ss"
                .FormatConditions(1).StopIfTrue = False
            End With
        End With
    End Sub
    
    Now that deletes any existing CF rules in the L2:L500 range before adding the new rule. If this is not there, running the macro multiple times will create duplicate rules.

    I thought if I could alter the macro code with what I need is the direction I should go. 

    Jean

    Thursday, May 11, 2017 1:18 PM
  • Hi

    Here is the link to the manual steps:

    https://www.mrexcel.com/forum/excel-questions/741581-visual-basic-applications-code-hide-pivot-table-subtotal-one-column-value-i-e-xldatafield.html

    Jean

    Thursday, May 11, 2017 1:33 PM
  • Hi

    I see I was not clear in my request.  I do not want to hide the caption.  I want to hide the sum values below the caption "Sum of Rate Total".  For example, $128.49, $321.00, $706.20.

    Once again I can do the steps manually but the recorded macro bombs.  I have found this issue at other forums but it is not based on the recorded macro that produced the manual steps.

    I am sorry about this confusion. 

    Jean


    • Edited by JAMHOME Thursday, May 11, 2017 1:54 PM should be that not the
    Thursday, May 11, 2017 1:51 PM
  • Hi

    Looks like I finally solved my question:  Here is the code:

    Sub DelSumRateTot()
    '
    ' DelSumRateTot Macro
    '
     Sheets("Signature").Select
    Range("$d$25").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        Selection.FormatConditions(Selection.FormatConditions.Count).NumberFormat = ";;;"
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions(1).ScopeType = xlFieldsScope
    End Sub
    Jean

    Thursday, May 11, 2017 7:57 PM
  • Hello Jean,

    I am glad that you resolved the issue. I suggest you mark it as answer to close the thread. Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by JAMHOME Friday, May 12, 2017 9:59 PM
    Friday, May 12, 2017 2:10 AM
    Moderator