HPageBreak count problem unless in page break view

T

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
 
V

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.
 
T

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
 

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