Excel has a feature called the usedrange - it does not store information on
cells outside this range. It is possible that your pagebreaks are outside
this range. You can find the bottom of this range with Edit=Goto=Special
and select Last Cell.


Related is that if you don't have anything to print past a pagebreak, it
isn't in the hpagebreak collection.

Here is some code you can play with: The output is in the immediate window
(in the VBE, choose View=Immediate Window or do Ctrl+G). This is from a
past posting I did:

-----------------------
Here is a method to get an array of horizontal pagebreaks and vertical
pagebreaks. The horizontal pagebreaks are a list of rows that have the
pagebreak and vertical a list of column numbers:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
Debug.Print J, horzpbArray(J)
Next J

i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i - 1)
Debug.Print "Vertical Pagebreaks (columns):"
For J = LBound(verpbArray, 1) To UBound(verpbArray, 1)
Debug.Print J, verpbArray(J)
Next J
End Sub

This uses an Excel 4 macro to get this information. This is much faster
than the VBA pagebreak which uses the printer driver and can be very slow.

The is a pagebreak property of the range. It can be tested to see if a
pagebreak exists

if rows(6).pagebreak = xlNone then
'No pagebreak
Else
' Has pagebreak
if rows(6).pagebreak = xlPageBreakAutomatic then
'Automatic pagebreak
elseif rows(6).pagebreak = xlPageBreakManual then
' Manual pagebreak
End if
End if


Combining the above gives:

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
Dim brkType As String
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1)
Debug.Print "Horizontal Pagebreaks (rows):"
For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1)
If Rows(horzpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, horzpbArray(j), brkType
Next j

i = 1
While Not IsError(Evaluate("Index(vPB," & i & ")"))
ReDim Preserve verpbArray(1 To i)
verpbArray(i) = Evaluate("Index(vPB," & i & ")")
i = i + 1
Wend
ReDim Preserve verpbArray(1 To i - 1)
Debug.Print "Vertical Pagebreaks (columns):"
For j = LBound(verpbArray, 1) To UBound(verpbArray, 1)
If Columns(verpbArray(j)).PageBreak = xlNone Then
brkType = "None"
Else
' Has pagebreak
If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then
brkType = "Automatic"
ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then
brkType = "Manual"
Else
brkType = "Unknown"
End If
End If

Debug.Print j, verpbArray(j), brkType
Next j
End Sub

Sample Output:
Horizontal Pagebreaks (rows):
1 13 Manual
2 24 Manual
3 39 Manual
4 67 Manual
5 87 Manual
6 114 Automatic
Vertical Pagebreaks (columns):
1 2 Manual
2 6 Automatic

This should get you started.

Regards,
Tom Ogilvy

"Rich" wrote in message
...
OK. That makes sense. Automatic pagebreaks. But I am
still baffled why if I try to loop through the pb
collection with something like

Dim pb as pagebreak 'or make pb a variant

For each pb in sht.hpagebreaks
debug.print pb.location
Next

this bombs out even if I did insert pagebreaks. If I
place the mouse cursor over pb in debug mode it shows

pb = nothing

even though the pb count is like 6. Any thoughts why For
Each..Next not working here?

Thanks for your reply,
Rich


-----Original Message-----
Sure you are not seeing automatic pagebreaks?

If the length of the page exceeds what can be printed on
the printer in one
page, excel puts in an automatic pagebreak. No way to
get around that other
than to put in manual pagebreaks that will fit to the
printer page.

--
Regards,
Tom Ogilvy

"Rich" wrote in message
...
Hello,

I was using an example in the Excel97 help files for
looping through a pagebreak collection, but the loop
didn't work.

For Each pb in Worksheets(1).HPageBreaks
...
Next

bombs out right at "For Each pb..." even though I do
have
pagebreaks in the worksheet. So I used a For..Next
loop.
This was a little better. But if I have 2 pagebreaks
that
I did insert, the sht.hpagebreak.count says like 6. So I
loop through the collection and I see a location for
phantom pagebreaks which I cannot remove/delete in
VBA. I
did this for several sheets in one workbook and got
different locations for these phantom pagebreaks. Is
this
a bug in Excle97 or is there a way to deal with these
underlying pagebreaks? Some obscure Excel constant
maybe? workbook configuration? Any suggestions
appreciated.

Thanks,
Rich


.