Pivot table row and column count

[Smithy88]

Hi,

I am trying to write a macro to get the number of rows and columns in a pivot table and then transfer that macro into Delphi. I can interact with pivottables using say

NoColsSFInPivot1 := Excel.ActiveSheet.PivotTablesPivotTableName).datafields.count;

which counts the datafields in the PT, but how can I count the number of rows and the number of columns?

Thanks

Paul

 

[Trevor G]

Paul I have created a Pivot table and placed it on a sheet. I have then used the following code to select the rows and then add a message box to count the rows and repeated it for counting the columns. I hope this helps.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> countPivot()<br><SPAN style="color:#00007F">Dim</SPAN> pt <SPAN style="color:#00007F">As</SPAN> PivotTable<br><SPAN style="color:#00007F">Set</SPAN> pt = ActiveSheet.PivotTables(1)<br>pt.RowRange.Select<br>MsgBox pt.RowRange.count<br>pt.ColumnRange.Select<br>MsgBox pt.ColumnRange.count<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Smithy88]

Hi,

Thanks, yes that works great, used it without the select. Well actually the rows seems to produce the correct count but the column count is coming through at +1 of the actual count for some reason. I have found

NoRowsSFInPivot1 := Excel.ActiveSheet.PivotTables(PivotTableName).TableRange2.Rows.Count;
NoColsSFInPivot1 := Excel.ActiveSheet.PivotTables(PivotTableName).TableRange2.Columns.Count;

but this gives the number of rows of the table entirely which is not always the number of rows. Column count always works though.

Thanks

Paul

 

[Trevor G]

Paul,

I hope it helps you find a solution. I suppose you can always get the count and take one off, obviously this also depends on the design of your Pivot.