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

You may also like...

10 Responses

  1. Doug Glancy says:

    Hi Debra, your code above isn’t formatted.

    I’ve been fooling around a lot with PivotSelect in order to improve my Per-Item PivotField Conditional Formatting code. It’s a tricky little area of VBA. Do you ever use PivotSelect?

    • Thanks Doug! I fixed the code formatting when I saw your comment, then wandered off, and forgot about your question. Sorry about that!
      I avoid PivotSelect as much as possible, because it’s such a pain. For PT conditional formatting, could you use the pivot item’s DataRange instead of PivotSelect?

      • Doug Glancy says:

        Debra, Using the DataRange works, except if I want to specify just an item’s Subtotal row or column. For that I can’t find a reliable way except for PivotSelect. It’s also just become something I’m trying to figure out. I’ve got a function that returns a PivotSelect string for the intersection of two pivot fields, with parameters for including their subtotals and separate value fields. If you know a reliable way to refer to a fields Subtotal row, if it has one, I’d be very interested to know.

        • Doug, you could check the pivot field’s SubtotalLocation property, and if it is 2 (subtotals at the bottom), add 1 row to the data range.

          • Doug Glancy says:

            Thanks Debra. I’m not sure that works. It needs to be the datarange for just a single pivot item, and those can reappear over multiple areas. Also, I have multiple subtotal levels, so it becomes very confusing. I think PivotSelect might actually be simpler (if not the only way) to pick up item-level instersections of data and totals.

            If I get something posted, I’ll comment back here, so you can see an example.

          • Sorry Doug, I hadn’t thought it through! I’ll wait to see what you figure out.
            I blame my head cold ;-)

          • Doug Glancy says:

            Hi Debra, I posted about my method for locating a PivotItem’s subtotals – including custom subtotals – at http://yoursumbuddy.com/locating-pivotitem-subtotals/. I ended up using the Range.PivotCellType property along with the Range.PivotItem property. I didn’t know about the first one of these until now. It seems to work for all situations.

          • Thanks. Doug! I’ll go and take a look.

  2. Tom says:

    Very usefull. Thanks for sharing

  3. Fernando says:

    Is it possible to do the same but with cubefields?

Leave a Reply to Doug Glancy Cancel reply

Your email address will not be published. Required fields are marked *