Pivot Table Range Address, Row & Column numbers

[Edmund]

VBA rookie here. I tried checking F1 & F2 for Help but cannot catch its
explanations for Pivot Table.

I'm stuck coz VBA does not seem to automatically list its members after the
priod.
"ActiveSheet.PivotTables(1).[no auto listing of members]"?

I tried:
"ActiveSheet.PivotTables(1).PivotFields(6).Datarange.Column" and ".Row". So
I found the address for the 1st cell of the ActiveSheet.PivotTables(1).

How can I get VBA to return:
(a) the last cell's address (or row or column) of ActiveSheet.PivotTables(1)
(b) the entire used range address of ActiveSheet.PivotTables(1)

Thanks a lot.

 

[Nick Hodge]

Edmund

You can always get members if you set the object to a variable, e.g

Sub GetMembers
Dim pvt as PivotTable
Set pvt = Worksheets(1).PivotTables(1)
pvt.[now members WILL list]
End Sub

I suspect you are looking for the TableRange1 or Table Range2 properties

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk