Finding automatic page breaks in new, blank worksheet

[eutocius]

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?

 

[Jim Cone]

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" <[email protected]>
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?