How to get print area borders - UsedRange not correct.

[JO]

I would like to get the cell range of the area that will be printed for
a worksheet when no print area is set. I can get the page breaks but
they do not include the beginning or ending boundaries. The beginning is
easy as I will be printing the entire worksheet. But the end has so far
eluded me.

I was using UsedRange but found this to not give the correct answer.
Case in point, I have a worksheet with 2 columns of data who range is
B4:C10. Then I have a chart generated from that data further down and to
the right on the worksheet. UsedRange returns A1:C10.

In Page Break Preview view mode there is a nice solid border for the
print boundary. What I need is this address of the cell at the bottom
right corner of this area. The chart's bottom right corner does not
match this location.

Any suggestions appreciated
TIA
JO

 

[Jim Rech]

Interesting question. I guess the only way to find the real last cell is to
work though the objects on the sheet like this:

Sub Test()
MsgBox RealLastCell.Address
End Sub

Function RealLastCell() As Range
Dim LastCell As Range
Dim Obj As Object
Dim LastRow As Long, LastCol As Long
Set LastCell = Cells.SpecialCells(xlCellTypeLastCell)
LastRow = LastCell.Row
LastCol = LastCell.Column
For Each Obj In ActiveSheet.DrawingObjects
If Obj.BottomRightCell.Row > LastRow Then
LastRow = Obj.BottomRightCell.Row
End If
If Obj.BottomRightCell.Column > LastCol Then
LastCol = Obj.BottomRightCell.Column
End If
Next
Set RealLastCell = Cells(LastRow, LastCol)
End Function