Home > Pivot > Macros > Pivot Fields Remove Pivot Fields With MacrosUse these sample macros to remove pivot fields from the pivot table layout. Read the instructions for using the macros, then get the code from each section, or download the free workbook. NOTE: If some pivot tables are OLAP-based (Data Model), use these OLAP macros instead. Also see: List all Pivot Fields with Details |
The macros shown below will remove pivot fields from the pivot table layout.
You can copy these macros to a regular code module in your workbook. Then, select a sheet that contains a pivot table, and run the macro.
If there are multiple pivot tables on the sheet, these macros will affect the first pivot table only.
Set pt = ActiveSheet.PivotTables(1)
If you would prefer to select a pivot table, and then run the macro, change the Set pt line to the following:
Set pt = Activecell.PivotTable
This macro will remove all the pivot fields from the pivot table layout. Be very careful if using this macro! Make a backup of your file first.
The ClearTable command in this macro will also remove calculated fields, calculated items, custom items and grouping. This is a macro version of use the Clear All command on the Ribbon's Design tab.
When you run the macro, if you see a warning message, click Cancel, if you don't want to go ahead with the clearing.
Sub SelPivotClear() 'CAUTION - clears layout, 'deletes grouping, calc fields & items Dim pt As PivotTable On Error Resume Next Set pt = ActiveSheet.PivotTables(1) pt.ClearTable End Sub
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.
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
This macro will remove all the column fields from the pivot table layout. If there are multiple Value fields in the pivot table, the Values button might be in the Column 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 Column area.
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
This macro will remove all the filter fields from the pivot table layout.
Sub RemoveAllFieldsFilter() Dim pt As PivotTable Dim pf As PivotField Set pt = ActiveSheet.PivotTables(1) For Each pf In pt.PageFields pf.Orientation = xlHidden Next pf End Sub
This macro will remove all the value fields from the pivot table layout. First, any calculated fields are removed, and then the remaining value fields are removed.
Sub RemoveAllFieldsValue() Dim pt As PivotTable Dim pf As PivotField Dim df As PivotField Set pt = ActiveSheet.PivotTables(1) 'remove calculated fields first, 'if any exist For Each pf In pt.CalculatedFields For Each df In pt.DataFields If df.SourceName = pf.Name Then With df .Parent.PivotItems(.Name) _ .Visible = False End With Exit For End If Next df Next pf For Each pf In pt.DataFields pf.Orientation = xlHidden Next pf End Sub
To see the code, and to test the macros, you can download the Remove Pivot Fields Macros file. The zipped file is in xlsm format, and contains macros. Be sure to enable macros when you open the file, if you want to test the code.
Remove Pivot Fields (Normal or OLAP)
Last updated: January 30, 2023 4:02 PM