CODEWITHSUNDEEP
vbaexcelpivot-table
Sean Bailey
Sean Bailey

Reputation: 375

Count Cols in Pivot Table VBA

All,

I have a pivot which varys in column width therefore I am trying to count the amount of columns in a pivot, I have the below code which errors on the line highlighted

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.ColumnRange.Count **ERROR**

Please can someone advise how to best count cols in a pivot table.

Upvotes: 1

Views: 5758

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

Try getting the number of columns with pt.TableRange2.Columns.Count

Code

Dim pt As PivotTable
Dim NumofCols As Long

Set pt = ActiveSheet.PivotTables(1)
NumofCols = pt.TableRange2.Columns.Count

If all you want to do is to make their column width fit, use the line below:

pt.TableRange2.EntireColumn.AutoFit

Upvotes: 3

Related Questions