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

Hi Vicky,

you could try this code:


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

'********Select the Pivot table*********
'using name of the pivot
'ActiveSheet.PivotTables("PivotTable4").PivotSelec t 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 Sub
--------------------



Hope this helps

tg


--
tg_evil
------------------------------------------------------------------------
tg_evil's Profile: http://www.excelforum.com/member.php...o&userid=37176
View this thread: http://www.excelforum.com/showthread...hreadid=568850

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.Cou nt
End Sub


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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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" wrote:

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.Cou nt
End Sub


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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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.

Vicky wrote:
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" wrote:


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.Cou nt
End Sub


Vicky wrote:

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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


"Debra Dalgleish" wrote:

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.

Vicky wrote:
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" wrote:


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.Cou nt
End Sub


Vicky wrote:

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


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html