Finding automatic page breaks in new, blank worksheet
 

When I create a new worksheet, it's blank and there are helpful dotted
lines indicating automatic page breaks. However if I run

debug.print ActiveSheet.HPageBreaks.Count

it returns 0 (same for VPageBreaks). The only way I've found to make
the page breaks "real" is to enter data beyond them. That is, after
typing something in a cell past the first horizontal line,
HPageBreaks.Count=1 (VPageBreaks.Count is still 0 until I enter data
to the right of the first dotted line).

This is a problem because I have written two functions that return the
height and width of a printable page as follows:

Public Function pageHeight() as Double
'Returns height of a printable area
Dim heightRange as String
heightRange = "A1:" & ActiveSheet.HpageBreaks(1).Location.Offset
(-1,0).Address
pageHeight = Range(heightRange).height
End Function

And analogously for width. However, these bust if I haven't
"activated" both the first horizontal and vertical pagebreaks.

Is there any way I can get find those breaks or directly call the
dimensions of that area programatically without fiddling with the
sheet every time?

Finding automatic page breaks in new, blank worksheet
 

Public Function pageHeight() as Double
If IsEmpty(ActiveSheet.Range("A333") Then
ActiveSheet.Range("A333").Value = "DuckSoup"
End If

'find page breaks

If ActiveSheet.Range("A333").Value = "DuckSoup" Then
ActiveSheet.Range("A333").Clear
End If
End Function
--
Jim Cone
Portland, Oregon USA



"eutocius"
wrote in message
When I create a new worksheet, it's blank and there are helpful dotted
lines indicating automatic page breaks. However if I run

debug.print ActiveSheet.HPageBreaks.Count

it returns 0 (same for VPageBreaks). The only way I've found to make
the page breaks "real" is to enter data beyond them. That is, after
typing something in a cell past the first horizontal line,
HPageBreaks.Count=1 (VPageBreaks.Count is still 0 until I enter data
to the right of the first dotted line).

This is a problem because I have written two functions that return the
height and width of a printable page as follows:

Public Function pageHeight() as Double
'Returns height of a printable area
Dim heightRange as String
heightRange = "A1:" & ActiveSheet.HpageBreaks(1).Location.Offset
(-1,0).Address
pageHeight = Range(heightRange).height
End Function

And analogously for width. However, these bust if I haven't
"activated" both the first horizontal and vertical pagebreaks.

Is there any way I can get find those breaks or directly call the
dimensions of that area programatically without fiddling with the
sheet every time?