print range bottom right cell

[Rosie189]

Is there a way to programaticlly determine the bottom right cell of the
worksheet print range when no print range has been declared. I've tried
using UsedRange but it doesn't recognize Shape Objects. HPageBreaks and
VPageBreaks tell me the top left but not the bottom right. Thanks.
--

 

[JLGWhiz]

From what I have been able to determine, the print area and the page break
have no relationship, except that the print area is contained within the page
break areas. Perhaps if you were more elaborate in what you want to do,
someone could offer a suggestion.

 

[Héctor Miguel]

hi, Rosie !

Is there a way to programaticlly determine the bottom right cell of the worksheet print range
when no print range has been declared.
I've tried using UsedRange but it doesn't recognize Shape Objects.
HPageBreaks and VPageBreaks tell me the top left but not the bottom right.
Thanks.

try a loop on your worksheet/s shapes collection (i.e.)

Sub Print_Area_NO_Data()
Dim n As Integer, mRow As Integer, mCol As Byte
With ActiveSheet
If .Shapes.Count = 0 Then Exit Sub
If .PageSetup.PrintArea <> "" Then Exit Sub
mRow = .Shapes(1).BottomRightCell.Row
mCol = .Shapes(1).BottomRightCell.Column
For n = 2 To .Shapes.Count
With .Shapes(n)
If .BottomRightCell.Row > mRow Then mRow = .BottomRightCell.Row
If .BottomRightCell.Column > mCol Then mCol = .BottomRightCell.Column
End With
Next
End With
MsgBox "Last printing cell is: " & Cells(mRow, mCol).Address
End Sub

hth,
hector.

 

[Rosie189]

Thanks Héctor. I'll incorporate your routine.

JLGWhiz, Thank you for also responding.

"Discussions" is such a great resource!