Remove Pivot Fields With a Macro

Instead of manually taking fields out of a pivot table, you can remove pivot fields with a macro. The sample macro below will remove all the row fields, and there are more examples, and a free workbook, on my website.

How to Use the Macro

To use this macro, copy the code to a regular code module in your workbook. Then, select a sheet that contains a pivot table, and run the macro.

NOTE: If there are two or more pivot tables on the sheet, the macro will affect the first pivot table only.

Set pt = ActiveSheet.PivotTables(1)

If you want to select a pivot table, and then run the macro, change the Set pt line to the following:

Set pt = Activecell.PivotTable

Remove All Row Fields

This macro will remove all the row fields from the pivot table layout.

If there are multiple Value fields in the pivot table, the Values button might be in the Row area. If so, the macro will show a message, asking if you want to remove the Values field too. Click Yes to remove the Values field, and click No to keep it in the Row area.

Remove Pivot Fields With a Macro – Rows

Copy the following code to a regular code module, then run the macro.

Sub RemoveAllFieldsRow()
Dim pt As PivotTable
Dim pf As PivotField
Dim lRmv As Long
Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.RowFields
  lRmv = vbYes
  If pf.Name = "Data" Or pf.Name = "Values" Then
    lRmv = MsgBox("Click Yes to remove Values field from Row area." _
      & vbCrLf _
      & "Click No to keep the Values field.", _
        vbQuestion + vbYesNo, "Remove Values?")
  End If
  If lRmv = vbYes Then
    pf.Orientation = xlHidden
  End If
Next pf

End Sub

More Macros to Remove Pivot Fields

You can see more examples to remove pivot fields with a macro, on my Contextures website. On the Remove Pivot Fields page, there is code to remove Column fields, Filter fields, Value fields and All Fields.

You can also download the free workbook on that page, with all the macros, and some test data. Scroll down to the Download section, and click the link, to get the workbook.

____________________

Save