Remove Pivot Fields with Macros

If you want to quickly remove all the pivot fields from one part of a pivot table, these macros will help. The sample macro below remove all the row fields, and there are more examples on my Contextures site, as well as a workbook that you can use for testing. Unlike the previous versions, these macros work with both normal pivot tables, and for OLAP-based pivot tables (Data Model).

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). If your workbook only has normal pivot tables, you can use the basic 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 these macros, different code is needed for OLAP-based pivot tables. The following function is used in the Remove Fields 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 Remove Fields macro.

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 removes all the row fields from the pivot table layout. However, if the Values button is in the Row area (representing multiple value fields), 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

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 – Macros page, there is code to remove Column fields, Filter fields, and Value fields. These macros work for normal pivot tables, and for OLAP-based pivot tables (Data Model).

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.

___________________

__________________________