Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range

Here’s a macro to locate the first and last rows and columns, and the ranges being occupied by your pivottable. The (0, 0) notation after the Address property statements is to omit the look of absolute “$” references for easier readability in the MessageBox.


Sub PivotTableRangeAreas()

With ActiveSheet.PivotTables(1)

Dim TopRow1 As Long, TopRow2 As Long, LastRow As Long
Dim LeftColumn As Long, RightColumn As Long

TopRow2 = .TableRange2.Row
With .TableRange1
TopRow1 = .Row
LastRow = .Rows.Count + .Row - 1
LeftColumn = .Column
RightColumn = .Columns.Count + .Column - 1
End With

MsgBox "The pivot table named " & .Name & vbCrLf & _
"occupies these range elements:" & vbCrLf & vbCrLf & _
"With the Report (Page) field: " & vbCrLf & _
.TableRange2.Address(0, 0) & vbCrLf & _
"Without the Report (Page) field: " & vbCrLf & _
.TableRange1.Address(0, 0) & vbCrLf & vbCrLf & _
"First row, with the Report (Page) field: " & TopRow2 & vbCrLf & _
"First row, without the Report (Page) field: " & TopRow1 & vbCrLf & _
"Last row: " & LastRow & vbCrLf & _
"Left column: " & LeftColumn & vbCrLf & _
"Right column: " & RightColumn, , "Pivot table location."

End With

End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
8 comments on “Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range
  1. Carol says:

    Thanks heaps Tom!
    I’ve searched heaps of googled sites and finally, someone who has helped find the pivot range, not just select it…

    • Tom Urtis says:

      I love seeing messages like this. Thanks a lot, Carol, for letting me know the code helped you out. I also post several Excel tips daily on Twitter @TomUrtis if you’re interested.

  2. Ramya says:

    Thank you so much, it helped a lot i was searhcing for this for a lot of time

  3. Danny says:

    I’ve got multiple tables on one sheet, how do I find out all this for a specific pivot please?

  4. Tom Spoors says:

    Hi Tom,

    I don’t know if you’re still tracking comments but this is the first time (I think ever) that a pasted in piece of code has done exactly what it says on the tin after pressing F5. Thank you. It’s given me clues in my search to set the sheet column widths (containing PivotItems) to the same value (the max) to make the Pivot table look good!

    • Tom Urtis says:

      Yes, I sure do still track all the comments on my blog pages. I’d like to think that all the examples I post work as intended; they all do when I tested them first but sometimes I make a mistake in my code or formulas and I want to hear about that too. Thanks for your comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

*