How to get print area borders - UsedRange not correct.
 

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

How to get print area borders - UsedRange not correct.
 

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

--
Jim
"JO" wrote in message
...
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