Pivot table double column remove

KittyS

New Member
Joined
May 25, 2012
Messages
18
Hello,

I am having some VBA trouble getting a double column removed from a pivot table. I have 2 columns like this:
Sum of NOV 2016 and Sum of NOV 2016_2. Sometimes I get a double month, so the content can vary, but the "_" is the constant.
I would like to get rid of this second column.
My code reads:
With Selection.PivotTable
Cells.Find(What:="_", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveCell.Orientation = xlHidden
End With
but that doesn't work. Is there some easy trick? :confused:
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi KittyS,

1. Are you trying to remove the 2nd field from just the PivotTable report or remove it from the availabile fields in the PivotTable field list?

2. What are the corresponding base field names in the data source?
 

KittyS

New Member
Joined
May 25, 2012
Messages
18
Hi Jerry,

Thanks for trying to help me out. As a reply to your questions:
1. I am trying to remove it from the report
2. the base field (column) name is NOV 2016.

Kitty
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Kitty, For the specific action you describe the code would look like this...

Code:
Sub HideSpecificDataField()
 With Selection.PivotTable
   .PivotFields("Sum of Nov-16_2").Orientation = xlHidden
 End With
End Sub

This code is a more generalized version that it sounds like you wanted.
It will hide all datafields that match a pattern like "*_2" or "*_#".

Code:
Sub HideDataFieldsMatchingPattern()
 '--hides data fields in selected pivottable that have
 '  names matching a specified pattern
 
 Dim pvf As PivotField
 Dim pvt As PivotTable
 Dim sErrMsg As String
 
 '--modify to match the pattern of field names to be hidden
 Const sPATTERN As String = "*_2"
 
 Application.EnableCancelKey = xlErrorHandler
 Application.EnableEvents = False
 
 On Error Resume Next
 Set pvt = Selection.PivotTable
 On Error GoTo ErrProc
 
 If pvt Is Nothing Then
   MsgBox "Select any cell on a PivotTable before running this macro."
   GoTo ExitProc
 End If
 
 For Each pvf In pvt.DataFields
   If pvf.Caption Like sPATTERN Then
      '--matches pattern - hide field
      pvf.Orientation = xlHidden
   End If
 Next pvf

ExitProc:
 On Error Resume Next
 Application.EnableEvents = True
 If Len(sErrMsg) Then MsgBox sErrMsg
 Exit Sub

ErrProc:
 sErrMsg = Err.Number & ": " & Err.Description
 Resume ExitProc
End Sub
 
Last edited:

KittyS

New Member
Joined
May 25, 2012
Messages
18
Hi Jerry,

Works like a charm! Thank you so much, saves me a ton of time!!

Kitty
 

Watch MrExcel Video

Forum statistics

Threads
1,133,531
Messages
5,659,360
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top