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
Thanks heaps Tom!
I’ve searched heaps of googled sites and finally, someone who has helped find the pivot range, not just select it…
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.
Thank you so much, it helped a lot i was searhcing for this for a lot of time
Thanks for letting me know it worked for you!
I’ve got multiple tables on one sheet, how do I find out all this for a specific pivot please?
I do not understand what this means:
“find out all this for a specific pivot”
Please explain further.
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!
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!