Is there a way to find range of a pivot table

user_1449545 0
Viewed 10276

Excel 2013. I have pivot table, which grows and shrinks according to the data rows. For example, 1. For the Row label, I have list of all products 2. For the Column label, I have list of manufacturers 3. For the Values, I have a count of 1 for the manufacturer. This would tell me how many manufacturers produces the same product.

Therefore the product list (row) grows and so does the Manufacturer list (columns) expands.

My question: Is there a way to get the range of the pivot, for example if the pivot tables starts at A1 and there are 10 products and 10 manufacturers, then the range of the pivot table would be $A$1:$K$12 (without Total for row or Total for column).

Is there a quick way to extract the range $A$1:$K$12?

Thank you in advance,

G.

pivot-table vba excel
2 Answers

To get the pivot table range that doesn't include the filters range, which is usually two rows above the table, use this code. This will select the actual pivot table range, and output the address of the range of the pivot table in a msg box.

Set pt = ActiveSheet.PivotTables(1)
pt.TableRange1.select
Msgbox pt.TableRange1.address
user_84533260

I see you tagged Excel-VBA.

Yes if you are able to consider Range as Range(cells(1,1),cells(12,12)) instead of Range("A1:K12")

There is a way to count field items in VBA.

Rcount = Sheets("YOUR SHEET NAME").PivotTables(1).PivotFields("YOUR ROW NAME").PivotItems.Count
Ccount = Sheets("YOUR SHEET NAME").PivotTables(1).PivotFields("YOUR COL NAME").PivotItems.Count

From there you would need only add 2 to compensate for extra rows/columns

Rcount = Rcount+2
Ccount = Ccount+2

Dim TableRNG as range
Set TableRNG = Range(cells(1,1),cells(Rcount,Ccount))

Hard to go any further without knowing what you are trying to accomplish and im not sure you were really asking for a VBA solution.

user_86517820
Related