pivottable result row count

What vba code can I use to get the number of rows of the  result?
I have tried the following code but none of them give me the answer "0".

? ActiveSheet.PivotTables("PivotTable1").DataBodyRange.Count()
1
? ActiveSheet.PivotTables("PivotTable1").TableRange2.Rows.Count
3
? ActiveSheet.PivotTables("PivotTable1").TableRange1.Rows.Count
3

Thanks!
ScreenHunter-01-2011-04-20-11.20.jpg
HobolyAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Here is an alternative. In your example the first column is A and the last header row is on 4 in Col A so you can use this to return number of rows

Sub Macro1()
    Dim LastRow As Long
    
    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    
    MsgBox LastRow - 4
End Sub

Open in new window


Note: xlUp in Pivot has to be used very carefully as it could give you unexpected results if there are no totals and there are multiple row fields.

Sid
0
 
SiddharthRoutCommented:
In your case an empty pivot will give you 3 rows

ActiveSheet.PivotTables("PivotTable1").TableRange2.Rows.Count

will give you 3

2 for the Headers and 1 for the 1st reserved row.

Sid
0
 
Rory ArchibaldConnect With a Mentor Commented:
You could use something like this:
   Dim pt As PivotTable, plist As PivotItemList
   Set pt = ActiveSheet.PivotTables(1)
   On Error Resume Next
   Set plist = pt.DataBodyRange.Cells(1).PivotCell.RowItems
   On Error GoTo 0
   If plist Is Nothing Then
      MsgBox "No data"
   End If

Open in new window

0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.