[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


pivottable result row count

Posted on 2011-04-19
Medium Priority
Last Modified: 2012-06-21
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()
? ActiveSheet.PivotTables("PivotTable1").TableRange2.Rows.Count
? ActiveSheet.PivotTables("PivotTable1").TableRange1.Rows.Count

Question by:Hoboly
  • 2
LVL 30

Expert Comment

ID: 35430175
In your case an empty pivot will give you 3 rows


will give you 3

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

LVL 30

Accepted Solution

SiddharthRout earned 1000 total points
ID: 35430190
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.

LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1000 total points
ID: 35430869
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

LVL 24

Expert Comment

ID: 35821810
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question