Pivot Table - How do I count number of columns?

[Guest]

I would like to be able to calculate how many columns are in my pivot table
report. Is there a tool within PivotTables?
Thanks

 

[tg_evil]

Hi Vicky,

you could try this code:


Code
-------------------
Sub pivot_count_columns()

'********Select the Pivot table*********
'using name of the pivot
'ActiveSheet.PivotTables("PivotTable4").PivotSelect PivotFields, xlDataAndLabel
'or using a known cell in the pivot
ActiveSheet.Range("q11").PivotTable.PivotSelect PivotFields, xlDataAndLabel

'if you want to count columns containing data only use xlDataOnly instead of xlDataAndLabel
'or if you want the number fo columns contining labels only use xlLabelOnly instead of xlDataAndLabel

'********Cont columns in selection**********
i = Selection.Columns.Count
'*********Check**********
MsgBox i

End Su
-------------------



Hope this helps

t

 

[Debra Dalgleish]

There's no built-in option that will count the columns. You could use
programming, e.g.:

Sub ColumnCount
MsgBox ActiveSheet.PivotTables(1).TableRange2.Columns.Count
End Sub

 

[Guest]

Debra and tg, Thanks. Would the same apply if you were wanting to count the
number of rows? Do I just replace the word columns with the word rows?
Thanks again.

 

[Debra Dalgleish]

Yes, you can replace Columns with Rows. If you use TableRange2, it
includes the Page area.
If you use TableRange1, it doesn't include the Page area.

 

[Guest]

Thanks, I am thrilled that I was able to figure out how to get it into VB on
my own!

Question - it seems to be counting the field names, grand totals too. Any
way to count just the items?

The below yielded a result of 12

Sum of Order Amount
Salesperson Total
Buchanan 68792.25
Callahan 123032.67
Davolio 182500.09
Dodsworth 75048.04
Fuller 162503.78
King 116962.99
Leverling 201196.27
Peacock 225763.68
Suyama 72527.63
Grand Total 1228327.4