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: , , , , , , , , , , , , ,
6 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?

Leave a Reply

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

*