pivottable result row count

Posted on 2011-04-19
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
    LVL 30

    Expert Comment

    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

    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
    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

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction This Article is a follow-up to my Mappit! Addin Article (, it was inspired by an email posting I made to EUSPRIG (, I will briefly cover: 1) An overvie…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now