Strange problem with HPageBreaks

[PO]

Hi,

I have a worksheet with 15 pages of data. ?ActiveSheet.HPageBreaks.Count
returns 14 in the immediate window.
The following code however produces an "Index out of range" error:

Sub FormatDoc()
Dim intI
Dim pb As HPageBreak
Dim rng As Range

For intI = 1 To ActiveSheet.HPageBreaks.Count
Set rng = ActiveSheet.HPageBreaks(intI).Location
ActiveSheet.Range(rng.Address).Resize(1,
10).Borders(xlEdgeTop).Weight = -4138
Next intI

End Sub

The strange thing is that the error occurs for different intI values (i.e.
intI =2 or intI = 5). And sometimes the whole procedure executes without
errors.
There are no inserted pagebreakes in the worksheet only those set by Excel.

Any ideas?

Regards
Pete

 

[JLGWhiz]

I did a little test and apparently, it does not count the last page break,
only the ones between pages.

 

[TomPl]

Very strange indeed.
If I scroll down to the bottom of the used range the macro works.
If I select PageBreak View the macro works.
If I open the workbook and leave the selected range at "A1" the macro gives
the "out of range" error. It seems that excel doesn't generate the page
breaks until focus is applied to the area to be printed.

Go figure?

 

[Ron de Bruin]

Hi Tom

This is a bug

See the link in this macro
http://www.rondebruin.nl/hpagebreaks.htm