Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    16
    Thanks
    0
    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!

    Tango

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    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
    Else
    pi.Visible = True
    End If
    Next pi
    pt.RefreshTable
    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
  •