Recomendamos: Remove Excel Pivot Fields With Macros

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.

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.

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

The post Remove Excel Pivot Fields With Macros appeared first on Contextures Blog.

Excel

via [Contextures Blog] [http://ift.tt/2hvWDBW]

December 14, 2016 at 04:18PM