Find the End of a Pivot table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Looping through - 21 Nov 2007 15:51 GMT

I need to find the end of a Pivot table I call "Won" the table sorts orders
out and is constantly growing. I need to find the last cell of that table at
any given time after it is filtered so I can add some notes and other info to
the bottom. I have tried range names but when the table grows it consumes my
Range Name as well. Can the cell with the range name move with the growing
table?

thanks
Peter

Reply to this Message

Jon Peltier - 21 Nov 2007 16:06 GMT

Sub GetLastPTCell()
Dim rLastCell As Range
With ActiveSheet.PivotTables(1).TableRange1
Set rLastCell = .Offset(.Rows.Count - 1, .Columns.Count - 1).Resize(1,
1)
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

>I need to find the end of a Pivot table I call "Won" the table sorts orders
> out and is constantly growing. I need to find the last cell of that table
[quoted text clipped - 8 lines]
> thanks
> Peter

Reply to this Message

Looping through - 21 Nov 2007 16:23 GMT

Jon, thanks for the responce, but the last cell of my pivot table was not
found. The code runs but the active cell does not get selected.

I added ActiveCell.Offset(0, -1).Range("A1").Select to your code to test.
this works but the active cell selected at the top of the PT not the bottom.

Peter

> Sub GetLastPTCell()
> Dim rLastCell As Range
[quoted text clipped - 23 lines]
> > thanks
> > Peter

Reply to this Message

Jon Peltier - 22 Nov 2007 14:52 GMT

To select the last cell of the pivot table:

Sub GetLastPTCell()
Dim rLastCell As Range
With ActiveSheet.PivotTables(1).TableRange1
Set rLastCell = .Offset(.Rows.Count - 1, .Columns.Count - 1).Resize(1,
1)
End With

rLastCell.Select

End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Jon, thanks for the responce, but the last cell of my pivot table was not
> found. The code runs but the active cell does not get selected.
[quoted text clipped - 38 lines]
>> > thanks
>> > Peter

Reply to this Message