PIVOT TABLE ADJUSTMENT

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

I really do not know macros or VBA. Is there a way to hide the rows “total” rows requested.

Thank you

Reply | Quote

Just RIGHT Mouse Click over any of the BLUE Rows and Choose HIDE off the Menu

See Attached where they are hidden.

Reply | Quote

Thank you

Reply | Quote

After pasting the code how does one get it to execute? It doesn’t show up as a macro. Trying to learn VBA.

Reply | Quote

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)

Reply | Quote

Right click one of the total lines.
Click on Field Settings
Click None.

Reply | Quote

Hello,

Well this is not pretty but it works.

Select entire row with total
Right click on the row
Select “Hide”

Reply | Quote

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

Reply | Quote