Identify PivotTable Last Cell 2

[Gavona]

I want to create a named range the bottom right corner of which is the bottom right cell of the pivot table. CAn anyone help with how to find the last cell. The following only works if I first reset the last cell in the workbook and second if there is nothing else in the workbook.

With ThisWorkbook.ActiveSheet
.Range(.Range("TopLeft"), .Range("TopLeft").SpecialCells(xlCellTypeLastCell)).Name = "rBudgetChanges"
End With

Thanks,

Gavin

 

[combo]

Some of the PivotTable object properties return ranges that refer to areas of pivot table report (see DataBodyRange, DataLabelRange, RowRange, ColumnRange, PageRange).

combo

 

[combo]

For the whole pivot table you can use either TableRange1 or TableRange2 properties (of PivotTable).
For a given range the PivotTable will return the table that contains the cell (or error if no pivot table).

combo

 

[SkipVought]

There are several properties that you could use:
[tt]
UsedRange - Worksheet property
CurrentRegion - Range property
TableRange1 - PivotTable property
TableRange2 - PivotTable property
[/tt]
So for any of these, the LAST row/col...

With WhateverRange
  LastRow = .Row + .Rows.Count - 1
  LastCol = .Column + .Columns.Count - 1
End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Thanks - those suggestions got me there.

UsedRange is I think equivalent to the lastcell approach with the same issues.

I tried TableRange1, TableRange2 yesterday but got an error and was convinced they must be unavailable in xl2003 however they work today.

I think the best solution for me is Skip's code along with either the DataBodyRange or TableRange? properties. With some adaptation my solution is:

Private Sub CommandButton1_Click()
'#########################################################
'# Refresh Pivot, Update column Widths and               #
'# define named range to be used by link in Word reports #
'#########################################################

Dim rBottomRight As Range
Application.ScreenUpdating = False

With ThisWorkbook.ActiveSheet
    .PivotTables(1).PivotCache.Refresh             'refresh the pivot
    ColWidthReset2 (.PivotTables(1).DataBodyRange) ' reset the column widths
    
    'Define NamedRange for embedding in Word Report
    With ActiveSheet.PivotTables(1).DataBodyRange
        Set rBottomRight = .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1)
    End With
    .Range(.Range("TopLeft"), rBottomRight).Name = "rBudgetChanges"
End With

Application.ScreenUpdating = True
End Sub

Gavin

 

[Gavona]

What happened there? Al;l the line feed / carriage return / new pararagraph characters seem to have been ignored. Teach me to preview!


Gavin

 

[Gavona]

Private Sub CommandButton1_Click()
'#########################################################
'# Refresh Pivot, Update column Widths and #
'# define named range to be used by link in Word reports #
'#########################################################
Dim rBottomRight As Range
Application.ScreenUpdating = False

With ThisWorkbook.ActiveSheet
.PivotTables(1).PivotCache.Refresh 'refresh the pivot
ColWidthReset2 (.PivotTables(1).DataBodyRange) ' reset the column widths

'Define NamedRange for embedding in Word Report
With ActiveSheet.PivotTables(1).DataBodyRange
Set rBottomRight = .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1)
End With
.Range(.Range("TopLeft"), rBottomRight).Name = "rBudgetChanges"
End With

Application.ScreenUpdating = True
End Sub

Gavin

 

[SkipVought]

ActiveSheet ALREADY referenced in outer With...End With

    With [s]ActiveSheet[/s].PivotTables(1).DataBodyRange
        Set rBottomRight = .Cells(1, 1).Offset(.Rows.Count - 1, .Columns.Count - 1)
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Good spot - thanks Skip.

Gavin

 

[xlbo]

there are issues with the code tag TGML processing currently
either don;t use the code tags or you need to use the tag on each line

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

thanks Geoff. I wondered what was going on.

Gavin

 

[xlbo]

looks like it's been fixed

shame the preview is broken though!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question