Remove Excel Pivot Fields With Macros

If you’re rearranging a complex pivot table, it can take a while to manually remove each field. To make the job easier, you can remove Excel pivot fields with macros. There’s a sample macro below that remove all the column fields, and you’ll find more examples, and a free workbook, on my website.

Remove Excel Pivot Fields With Macros – Columns

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

If the pivot table has more than one Value field, a Values button is created – you’ll see it in the PivotTable Field List.

macroremovefields03

By default, that button is in the Column area, so if you haven’t moved it, the macro will show a message, asking if you want to remove the Values field.

Click Yes if you want to remove the Values field, and click No to keep the values.

Remove Excel Pivot Fields With Macros

The Macro Code

To use the macro, copy the following code to a regular code module, then run the macro. See the next section for more details.

Sub RemoveAllFieldsCol()
Dim pt As PivotTable
Dim pf As PivotField
Dim lRmv As Long

Set pt = ActiveSheet.PivotTables(1)

For Each pf In pt.ColumnFields
  lRmv = vbYes
  If pf.Name = "Data" Or pf.Name = "Values" Then
    lRmv = MsgBox("Click Yes to remove Values field from Column 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

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

More Macros to Remove Pivot Fields

You can find more macros to remove pivot fields, on the Remove Pivot Fields page on my Contextures website. There is code to remove Row 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

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.