PivotTable.TableRange is returning the wrong value!

[TheRedCardinal]

I am trying to determine the number of rows of data in a Pivot field.

My code looks like this:

Sub LookupCommon()


'A sub to populate the CSV file with common data


Set WS1 = Sheets("5. Final CSV")
Set WS2 = Sheets("4. PivotTable")


Set MyPivot = WS2.PivotTables(1)
Counter = MyPivot.TableRange1.Rows.Count


End Sub

This returns a value for "Counter" of 76. My table has 73 rows of data. It also has a Grand Total Row, and two rows at the top for the various names = 76.

When I re-run my code with TableRange2.Rows.Count instead, I also get 76.

Surely these 2 numbers cannot be the same? I thought TableRange1 would be data only, and TableRange2 for the whole page?

 

[=ODIN=]

You are misunderstanding what those properties do. They either include or exclude the page filter of a pivot table when telling you where the pivot table sits on a worksheet. If you have no page filter, then they will be identical -- and they will include and subtotal/grandtotal rows. It seems like what you are really looking for might be a count of the items within a pivot field.

[COLOR=#333333]Counter = MyPivot.pivotfields("aPivotFieldName").pivotItems.count[/COLOR]

This thread might help: https://www.mrexcel.com/forum/excel-questions/943581-vba-count-visible-pivot-items.html

 

[TheRedCardinal]

Hi - yes I was misunderstanding and thanks for the explanation. I had interpreted Range1 and being data and Range2 as being all the fluff around the edges, not just the filters.

Thanks for the explanation!