Pivot Table - How do I count number of columns?

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:

Please Login or Register  to view this content.

Hope this helps

tg

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


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.Count
> 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.Count
>>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.Count
> >>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
>
>