Print Ranges when Print_Area not set

[Dominic Robinson]

How do you determine in VBA the range that will be printed
on a worksheet when the print area for that sheet has not
been set?

I would expect some kind of PrintRange property of the
worksheet.pagesetup object but I have not been able to
find anything like this. (I remember something similar in
the old Excel Macro language?)

As the application places a dotted line around the Print
range (after the first preview) I would expect that this
range would be available.

In a multiple page worksheet I can determine the ranges of
all but the last page using the HPageBreaks and
VPageBreaks property of the worksheet object. However, I
cannot determine the range of the last page.

Any ideas?

 

[Jan Karel Pieterse]

Hi,

If no print area is set, the Printarea is empty, but Excel
then by default prints everthing between cell A1 and the
last cell that is in use. So:

Sub test()
Dim sArea As String
sArea = ActiveSheet.PageSetup.PrintArea
If sArea = "" Then
With ActiveSheet
sArea = .Range("a1", .Cells.SpecialCells
(xlLastCell).Address).Address
End With
End If
MsgBox sArea
End Sub


Regards,

Jan Karel Pieterse
Excel TA/MVP