Results 1 to 2 of 2
  1. #1
    New Lounger
    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
    Next c

    Thanks for the help!


  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    Silver Lounger
    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
    <pre>Option Explicit
    Sub UpdatePivotTableDisplay()
    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
    End If
    Next pi
    Set pf = Nothing
    Set pt = Nothing
    End Sub</pre>

    <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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts