Home > Pivot > Macros > Pivot Fields Remove Pivot Fields - Macros Use 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

Using 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.

Check for OLAP

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.

• The function returns True, if the source for the pivot cache is OLAP-based, such as a Data Model pivot table.
• For normal pivot tables, the function returns False.

IsOLAP Function Code

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

Remove All Row Fields

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.

Remove All Row Fields Code

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.

• If the field name is "Data" or "Values", nothing will happen.
• For other fields, the field's orientation is set to Hidden, so it's no longer in the Rows area.

For OLAP-based pivot tables (IsOLAP=True), the code loops through each field in the Rows area.

• If the field name begins with "[Measure]", or is "Data" or "Values", nothing will happen.
• For other fields, the cf variable is set to the field's CubeField
• The cubefield's orientation is set to Hidden, so it's no longer 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

Remove All Column Fields

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 

Remove All Filter Fields

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

Remove All Value Fields

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

Download the Sample File

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.

More Pivot Table Resources

FAQs - Pivot Tables

Excel Slicer Macros

Pivot Table Introduction

List all Pivot Fields with Details

Grouping Data

Summary Functions

Clear Old Items in Pivot Table