When you create a pivot table, it is based on a pivot cache, with all of the records from the source data table. You can’t see the pivot cache, but with some programming, you can get information about the cache.
In the pivot table shown above, you can see the total quantities for all the records in the source data. To see how many records are in that source data, you can create a User Defined Function.
Create a User Defined Function
To create the User Defined Function, copy the following code, and paste it into a regular code module.
Function GetRecords(rngPT As Range) As Long
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Set pt = rngPT.PivotTable
GetRecords = ActiveWorkbook _
.PivotCaches(pt.CacheIndex).RecordCount
End Function
This creates a function named GetRecords, and it requires a cell reference as its argument. If the referenced cell is in a pivot table, the function will show the record count for that pivot table’s pivot cache.
Note: If you save this file, make it a macro-enabled file type.
User a User Defined Function
Then, on a worksheet in that workbook, enter a formula that uses the function name, and refers to a cell in the pivot table. For example, enter the following formula in cell B1, referring to the pivot table in cell A3:
=GetRecords(A3)
The formula result is 825, which is the number of records in the source data. You can see the last record in the screen shot below. It’s in row 826, and if you subtract 1 for the heading row, that is record number 825.
______________