locked
How to get the List of Visible Items from a Pivot Field using VBA RRS feed

  • Question

  • I'm using the below code to loop through a pivot field for getting the pivot items which are visible. But the pivotitem.count gives a 0 when the pivot field is in Row Label When I move this pivot field to Column Label, the code is working fine. But I NEED this Field to stay row label. Is there any work around for my problem?

    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pvtitem As PivotItem
    
    Set nwSheet = Worksheets.Add
    nwSheet.Activate
    rw = 0
    
    Set pt = Sheets("Reasons").PivotTables("PivotFields")
    Set pf = pt.PivotFields("[Characteristics].[Reason].[Reason]")
    
    With pf
        For i = 0 To .PivotItems.Count
            rw = rw + 1
            nwSheet.Cells(rw, 1).Value = .PivotItems.Count
        Next i
    End With

    Pramod

    Monday, August 1, 2016 8:02 PM

Answers

  • Hello,

    I can't see you're adressing your rowfields.

    I think you got just the pivotField, but not in state as a rowfield, so maybey that's the reason why you have no count?


    Maybey you could try something like:

    Dim pi As PivotItem

    For Each pi In pt.RowFields(1).PivotItems   

    If pi.Visible = True Then       

    'do your thing       

    Debug.Print pi.Value   

    End If

    Next


    (With  pt.RowFields(1).PivotItems: [Characteristics].[Reason].[Reason] )

    Hope it helps

    Tuesday, August 2, 2016 3:00 PM

All replies

  • Hello,

    I can't see you're adressing your rowfields.

    I think you got just the pivotField, but not in state as a rowfield, so maybey that's the reason why you have no count?


    Maybey you could try something like:

    Dim pi As PivotItem

    For Each pi In pt.RowFields(1).PivotItems   

    If pi.Visible = True Then       

    'do your thing       

    Debug.Print pi.Value   

    End If

    Next


    (With  pt.RowFields(1).PivotItems: [Characteristics].[Reason].[Reason] )

    Hope it helps

    Tuesday, August 2, 2016 3:00 PM
  • Thank You Wouter It's working now.


    Pramod


    Tuesday, August 2, 2016 8:39 PM
  • Hi Wouter, Can you please take a look at this question as well: https://social.msdn.microsoft.com/Forums/en-US/f060e40e-1273-4802-a2fa-39f3742c5674/how-to-iterate-through-the-pivot-items-in-pivot-field-inside-report-filter?forum=isvvba

    Pramod

    Wednesday, August 3, 2016 3:53 PM