Strange problem with HPageBreaks

P

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
 
J

JLGWhiz

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

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top