[pivot table] Get rows total column with VBA

[Lece]

Hi!

I need to access all rows' total column in a Pivot Table with VBA. Something similar to

.PivotFields("MyColumns").PivotItems("Madrid").DataRange.Select

but only with the Total column

What I have is:


What I need is:


I know I can do something like this:

Range(4, 1).Select
Selection.End(xlToRight).Select
' Skip one cell down with .Offset here...
Range(Selection, Selection.End(xlDown)).Select
' Skip one cell up with .Offset here...

but I need a more flexible solution -- something like with Madrid above.

Tried to search for an answer here http://peltiertech.com/WordPress/referencing-pivot-table-ranges-in-vba/ but didn't find any.

Please help me! Thank you

 

[Jerry Sullivan]

Hi Lece,

Try...

Sub SelectTotalColumns()
 
    Dim lColFields As Long
 
    With ActiveSheet.PivotTables("PivotTable1")
        lColFields = .ColumnFields.Count
        If .RowGrand = False Or lColFields = 0 Then Exit Sub
        With .DataBodyRange
            .Resize(, lColFields).Offset(0, .Columns.Count - lColFields).Select
        End With
    End With
End Sub

 

[Lece]

Hi JS411

Your code worked but I didn't need the total row selected so this is how I edited it:

    With ActiveSheet.PivotTables(1)
        lColFields = .ColumnFields.Count
        If .RowGrand = False Or lColFields = 0 Then Exit Sub
        With .DataBodyRange
            .Resize([B].Rows.Count - 1[/B], lColFields).Offset(0, .Columns.Count - lColFields).Select
        End With
    End With

Thank you very much for your help!