Home > Pivot > Macros > Pivot Fields Remove Pivot Fields - MacrosUse these macros to remove pivot fields from a pivot table - either normal or data model. See the instructions for using these macros, and copy the code below, or get the free workbook that has test pivot tables and the macros |
The macros shown below will remove pivot fields from a pivot table, whether is is a normal pivot table, or based on the Excel Data Model (OLAP-based).
NOTE: If your workbook only has normal pivot tables, you can use the Remove Pivot Fields macros instead.
To use these macros, copy them to a regular code module in your workbook. Then, select a cell in a pivot table, and run the macro.
In some situations, different code is needed for OLAP-based pivot tables. This function is used in the macros, to check for OLAP-based Source Data.
Copy this function code to the workbook with the other macros from this page.
Function IsOLAP(myPT As PivotTable) As Boolean 'checks for OLAP data source, e.g. Data Model 'used in the macros below IsOLAP = False If myPT.PivotCache.OLAP Then IsOLAP = True End If End Function
This macro will remove all the row fields from the pivot table layout.
NOTE: If there are multiple Value fields in the pivot table, the Values button might be in the Row area. This macro will not remove that Values field.
First, the code checks if a pivot table cell is selected. If not, a message appears - "Please select a pivot table cell then try again."
Next, the macro calls the IsOLAP function, to check if the pivot table is OLAP-based.
For normal pivot tables (IsOLAP=False), the code loops through each field in the Rows area.
For OLAP-based pivot tables (IsOLAP=True), the code loops through each field in the Rows area.
Sub RemoveAllRowFields() Dim pt As PivotTable Dim pf As PivotField Dim cf As CubeField 'select a pivot table cell ' before running this macro 'does not remove Values field 'if it is in the Row area 'select pivot table cell first On Error Resume Next Set pt = ActiveCell.PivotTable If pt Is Nothing Then MsgBox "Please select a pivot table cell" _ & vbCrLf _ & " then try again." Exit Sub End If If IsOLAP(pt) = False Then For Each pf In pt.RowFields If pf.Name <> "Data" _ And pf.Name <> "Values" Then pf.Orientation = xlHidden End If Next pf Else For Each pf In pt.RowFields If Left(pf.Name, 10) <> "[Measures]" _ And pf.Name <> "Data" _ And pf.Name <> "Values" Then On Error Resume Next Set cf = pf.CubeField On Error GoTo 0 If Not cf Is Nothing Then cf.Orientation = xlHidden End If End If Next pf End If End Sub
To create a macro that removes all the column fields from the pivot table layout, make a copy of the Remove All Row Fields macro (above).
Then, change the following line:
For Each pf In pt.RowFields
To this, so it loops through the Column Fields, instead of the Row Fields:
For Each pf In pt.ColumnFields
This macro will remove all the filter fields from the pivot table layout. It is similar to the Remove All Row Fields macro (above), but does not check for the "Values" field. That cannot be located in the Filters area.
Sub RemoveAllFilterFields() Dim pt As PivotTable Dim pf As PivotField Dim cf As CubeField 'select a pivot table cell ' before running this macro On Error Resume Next Set pt = ActiveCell.PivotTable If pt Is Nothing Then MsgBox "Please select a pivot table cell" _ & vbCrLf _ & " then try again." Exit Sub End If If IsOLAP(pt) = False Then For Each pf In pt.PageFields pf.Orientation = xlHidden Next pf Else For Each pf In pt.PageFields On Error Resume Next Set cf = pf.CubeField On Error GoTo 0 If Not cf Is Nothing Then cf.Orientation = xlHidden End If Next pf End If End Sub
This macro will remove all the value fields from the pivot table layout. It is similar to the Remove All Row Fields macro (above), but for normal pivot tables, calculated fields are removed first. That step is not necessary for OLAP-based pivot tables, because they cannot contain calculated fields.
However, OLAP-based pivot tables have trouble with the "Values" field in the Values area, so a check for that is included.
Sub RemoveAllValueFields() Dim pt As PivotTable Dim pf As PivotField Dim df As PivotField Dim cf As CubeField 'select a pivot table cell ' before running this macro On Error Resume Next Set pt = ActiveCell.PivotTable If pt Is Nothing Then MsgBox "Please select a pivot table cell" _ & vbCrLf _ & " then try again." Exit Sub End If If IsOLAP(pt) = False Then '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 Else For Each pf In pt.DataFields If pf.Name <> "Data" _ And pf.Name <> "Values" Then On Error Resume Next Set cf = pf.CubeField If Not cf Is Nothing Then cf.Orientation = xlHidden End If On Error GoTo 0 End If Next pf End If 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.
Last updated: March 22, 2023 12:08 PM