Loop through column totals in pivot table with vba

[whitehawk81]

Hi,
I have a question regarding pivot tables. I would need to loop through the column grand total pivot items for each pivot table on a worksheet, to see if the values are greater than 0. But I couldn't find anything helpful so far. Could someone help?

 

[whitehawk81]

I managed to get the row index of the last row in the pivot table, but I still cannot define the last row as range.
This is how the code looks right now:

Sub listUsedCharts(ws As Worksheet, pt As PivotTable)Dim i, n, lRow As Long
Dim ptName As String
Dim osTbl As ListObject
Dim pf As PivotField
Dim rngTtl, ptData As Range


n = ws.Cells(Rows.Count, 1).End(xlUp).row
Set osTbl = ws.ListObjects("KBOS")


For i = 1 To ws.PivotTables.Count
ptName = "SolPT" & i
Set pt = ws.PivotTables(ptName)
Set pf = pt.PivotFields("Solution")
[COLOR=#ffa500]Set ptData = pt.DataBodyRange[/COLOR]


[COLOR=#ffa500]With ptData[/COLOR]
[COLOR=#ffa500]lRow = .Rows.Count[/COLOR]
[COLOR=#ffa500]Set rngTtl = .Cells.Rows(lRow)[/COLOR]
[COLOR=#ffa500]End With[/COLOR][COLOR=#ffa500][/COLOR]


Debug.Print ptName & "," & lRow & "," & rngTtl.Value & "," & rngTtl.Address
    
If i < 7 Then
    If pf.CurrentPage <> "(All)" And ptData.Count > 1 Then
        ws.Cells(n, 1).Value = i
        n = n + 1
    End If
ElseIf i = 7 And pf.CurrentPage <> "(All)" Then
    ws.Cells(1, 2).Value = True
    lRow = ptData.Count + 1
    osTbl.Resize ws.Range("$AH$1:$AO$" & lRow)
Else
    ws.Cells(1, 2).Value = False
End If
Next i


End Sub