You are here:

Excel/Selecting specific pivot items with VBA

Advertisement


Question
Dear Tom

How do you de-select 'ALL' the pivot items say a list of wild animals and then select specific pivot items say, Lion, Crocodile and Tiger using VBA? An example would be very much appreciated.

Thank you.

Answer
Francis Ang,

You actually don't want to de-select "ALL"  A pivotfield should always have at least on visible item or you will get an error.  You would do the opposite.   Make all items visible, then hide the items you don't want.

Here is some tested code that worked for me.  I had one row field ("header1") which had items
A, B, C, D, E, F, G, H, I

and I wanted to select items  A, F, G

This code did that:

Sub abc()
Dim pt As PivotTable, pf As PivotField
Dim itm As PivotItem, vArray As Variant
Dim bFound As Boolean, i As Long
Set pt = ActiveSheet.PivotTables("pivottable1")
Set pf = pt.PivotFields("Header1")
ReDim vArray(1 To 3)
' list of items I want visible
vArray(1) = "A"
vArray(2) = "F"
vArray(3) = "G"
pt.ManualUpdate = True
pf.ClearManualFilter   '<== makes all items visible
For Each itm In pf.PivotItems
bFound = False
For i = LBound(vArray) To UBound(vArray)
  If itm.Value = vArray(i) Then
   Debug.Print itm.Value, i, vArray(i)
   bFound = True
  End If
Next
If bFound = False Then
  itm.Visible = False
End If
Next
pt.ManualUpdate = False
End Sub


I have used this command

pt.ManualUpdate = True

before I make any changes.  If you don't do that, then each change will be made immediately.  In a small pivot table this isn't a problem.  But in a larger table, it will take a long time updating for each change.

At the end of the changes I set it back to

pt.ManualUpdate = False

and the pivot table updates one time.  

--
Regards,
Tom Ogilvy
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.