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

by Tom Urtis 8 Comments ↓

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

‹ Tom’s Tutorials For Excel: Explaining Array Formulas

Tom’s Tutorials For Excel: What time is it *Really*? Ask the US Navy With a Web Query ›

Posted in Tom's Tutorials for Excel
Tags: Analytics, Big Data, Business Intelligence, Cells Ranges Lists, Data Science, Excel Expert, Excel Guru, Macros Events Classes API, Microsoft, Microsoft Excel, Microsoft MVP, Microsoft Office, Pivot Tables / Pivot Charts, Tom Urtis

8 comments on “Tom’s Tutorials For Excel: Locating Your PivotTable’s Occupied Range”

1. Carol says:

   July 2, 2018 at 7:54 pm

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

   Reply
   • Tom Urtis says:

     July 2, 2018 at 8:07 pm

     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.

     Reply
2. Ramya says:

   March 26, 2019 at 12:57 am

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

   Reply
   • Tom Urtis says:

     March 26, 2019 at 2:13 am

     Thanks for letting me know it worked for you!

     Reply
3. Danny says:

   January 20, 2020 at 6:37 am

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

   Reply
   • Tom Urtis says:

     January 20, 2020 at 8:19 am

     I do not understand what this means:
     “find out all this for a specific pivot”

     Please explain further.

     Reply
4. Tom Spoors says:

   July 7, 2023 at 7:24 am

   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!

   Reply
   • Tom Urtis says:

     July 7, 2023 at 8:01 am

     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!

     Reply