CODEWITHSUNDEEP
excelvbapivot-table
Pherdindy
Pherdindy

Reputation: 1178

How to loop through each active (non filtered) element in a Pivot Table (excluding the column labels and grand totals)?

I was expecting the fields to be "Sum of Reg Hrs.", "Sum of Reg OT Hrs.", ... , "Sum of SNWH NSD Hrs."

But what I got was MsgBox pf shows "Values"

Msgbox pi shows "Sum of Reg Hrs.", "Sum of Reg OT Hrs.", ... , "Sum of SNWH NSD Hrs."

But ideally, I want pi to be the items of each column. Is there another method to do so?

Sub test()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = ThisWorkbook.Worksheets("N-MR Hours Summary").PivotTables("N-MR Hours Summary")

For Each pf In pt.ColumnFields
    MsgBox pf
    For Each pi In pf.PivotItems
        MsgBox pi
    Next pi
Next pf

End Sub()

enter image description here

Also, it should only include, the active cells (non filtered cells), but I realized even the filtered cells are iterated about.

Upvotes: 1

Views: 645

Answers (1)

Asger
Asger

Reputation: 3877

A pivot table has a PivotRowAxis (vertical) and a PivotColumnAxis (horizontal).
They each consist of one or more PivotLines.
Following screenshot explains both axis and their PivotLines with PivotLineCells.

PivotLine Explanation

In your example, each PivotColumnAxis.PivotLine only has 1 PivotLineCell.
The PivotLine.PivotLineType can be used to identify rows or columns with regular values, sub totals, grand total or completely blank lines:

PivotLineType Explanation

As your columns only consist of DataFields (no dedicated ColumnFields), you can loop over PivotColumnAxis.PivotLine(i).PivotLineCells(1).PivotField to get the vertical DataRange of each column:

PivotLineCell with PivotField and its DataRange

Here's the code for it:

Private Sub ColumnByColumn()
    Dim pt As PivotTable
    Dim pl As PivotLine
    Dim pf As PivotField
    Dim c As Range

    Set pt = ActiveSheet.PivotTables(1)
    For Each pl In pt.PivotColumnAxis.PivotLines
        If pl.LineType = xlPivotLineRegular Then
            Set pf = pl.PivotLineCells(1).PivotField
            If pf.Orientation = xlDataField Then
                Debug.Print pf.Caption,
                For Each c In pf.DataRange.Cells
                    Debug.Print c.Value,
                Next c
                Debug.Print
            End If
        End If
    Next pl
End Sub

Further hints:

Your pivottable only contains DataFields (no explicit ColumnFields), therefore the pivotfield's name is returned as "Values".

Unfortunately it's never simple to loop over all visible pivotitems, as a simple loop over all of them also returns the invisible (filtered) items. Even PivotField.VisibleItems.Count always returns the same value as PivotField.PivotItems.Count.

PivotLine.LineType is in your case always xlPivotLineRegular (It could also be xlPivotLineBlank, xlPivotLineSubtotal or xlPivotLineGrandTotal if you would have blank columns, subtotal columns or grand total columns).

Upvotes: 1

Related Questions