Thread: Hiding Pivotitems (VB/VBA)
2003-06-30, 18:59 #1
- Join Date
- Mar 2003
- Thanked 0 Times in 0 Posts
Hiding Pivotitems (VB/VBA)
I'm trying to use code to set certain values in a list to not show up on a pivot table....for some reason, this doesn't work. I've defined the range myvalues and the myfind variable and the c variable are being passed correctly to the code, but it won't turn them off (make them not visible on the Pivottable)... is there something I'm missing.....
For Each c In Range("myvalues")
myfind = Cells.Find(What:=c, LookAt:=xlWhole).Address
If Not (IsEmpty(myfind)) Then mytable.RowFields("Subcat Desc").PivotItems©.Visible = False
Thanks for the help!
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2003-07-01, 06:12 #2
- Join Date
- Mar 2001
- Springfield, Ohio, USA
- Thanked 1 Time in 1 Post
Re: Hiding Pivotitems (VB/VBA)
Had trouble completly understanding your code. As much as I could tell. it looked OK. If this post dosn't help, maybe you could reply with a simple worksheet example attached. It could be something simple, like did you do mytable.RefreshTable?
Anyway, I've attached a (hopefully) simple example of what I think that you wanted to do. For those that do not like to open attachments, here is the code. You will need to adapted it to use your names. HTH --Sam
Dim pt As PivotTable, pf As PivotField
Dim pi As PivotItem, c As Range
Set pt = ActiveSheet.PivotTables("ptPhones")
Set pf = pt.PivotFields("Area Code")
For Each pi In pf.PivotItems
Set c = ActiveSheet.Range("Area_Codes_To_Display").Find(pi .Name)
If c Is Nothing Then
pi.Visible = False
pi.Visible = True
Set pf = Nothing
Set pt = Nothing
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>