HPageBreak count problem unless in page break view

[Tim Zych]

I am trying to run a page setup macro. The first part is simple. It does a
page setup as you can see.
The 2nd part is where I am having problems. The macro only seems to work
when I start out in normal page view. Before the 2nd part is executed (I put
a stop in while testing), I must change to page break view, and then
complete the macro.

Weird thing is, if I set the sheet to page break view before starting the
macro, the loop continues one more than the count of the HPageBreaks. In
other words, if I have 4 HPageBreaks, the loop completes 4 and then loops
through a 5th time.

Consequently, it returns a runtime error "9: Subscript out of range" for the
5th loop since it can't refer to HPageBreaks(5) when HPageBreaks.Count = 4.

In the immediate window during the error:

?wks.HPageBreaks.Count
4
?i
5

Anyone know what the heck is going on? Am I setting HPageBreaks incorrectly?

Thanks.

Sub DoPageSetup()

Dim i As Integer, wks As Worksheet, cell As Range
Set wks = ActiveSheet

'Only works if I start out in normal page view mode ''''''

With wks.PageSetup
.PrintTitleRows = "$1:$4"
.CenterFooter = "&P of &N"
.RightFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

'Then I must view page breaks to get this part to work '''''''

For i = 1 To wks.HPageBreaks.Count
Set cell = wks.HPageBreaks(i).Location
Set cell = GetNextNonblankCellUp(cell)
Set wks.HPageBreaks(i).Location = cell
Next

End Sub

'returns the next non-blank cell up from a starting range position
Function GetNextNonblankCellUp(ByVal pcell As Range) As Range
Dim cell As Range
Set cell = pcell
Do
Set cell = cell.Offset(-1)
Loop Until Len(cell.Formula) > 0
Set GetNextNonblankCellUp = cell
End Function

 

[Vasant Nanavati]

Hi Tim:

My experience with HPageBreaks collection is that it is buggy. It has to
somehow be initialized (by printing or previewing or scrolling through)
before it is "set". Of course, this applies to VPageBreaks as well.

I have not seen an elegant solution that gets around this. There are some
Excel 4 macros that apparently work better, but I don't have them handy.

Regards,

Vasant.

 

[Vasant Nanavati]

Just found some code from Myrna Larson which may be helpful:

http://google.com/[email protected]


Vasant

 

[Tim Zych]

Thanks for the link Vasant. I ended up using my approach (more polished than
what I posted) since it seemed to work.

I appreciate the validation of the unusual behavior from the info in the
link.

tim