Page Break: Format Row

[JR_06062005]

In the following macro I have inserted a print preview to reset the
horizontal page break counter in the spreadsheet. Without this accomodation
I can only run the macro once. The second time I run it, I get an out of
range error.

Is there another way to reset the horizontal page break so that each time
the macro runs it starts recognizing the horizontal page breaks from the
beginnging of the spreadsheet?

Sub LineFormatAtPageBreak()
Dim oHPgbr As HPageBreak

ActiveWindow.SelectedSheets.PrintPreview

For Each oHPgbr In ActiveSheet.HPageBreaks
MsgBox "Other row page break at:" & oHPgbr.Location.Address
' Format row at page break
Next
End Sub

 

[SeanC UK]

Hi JR (OK to call you that?),

I haven't yet worked out exactly why it behaves this way - I found the same
thing - but I believe this shoul work, it solved it for me.


Sub LineFormatAtPageBreak()
Dim oHPgbr As HPageBreak
Debug.Print ActiveSheet.HPageBreaks.Count
ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).Select
ActiveSheet.Cells(1, 1).Select
For Each oHPgbr In ActiveSheet.HPageBreaks
MsgBox "Other row page break at: " & oHPgbr.Location.Address
' Format row at page break
Next
End Sub

I have used the Debug line to force the pagebreaks to show before the For
Each line. Anything that forces these to appear will do. Then I have selected
the last cell in column 1, before returning to the top. It appears that once
a cell has been selected below the final HPageBreak the problem goes. So far
I have tested a few times with different scenarios and this hasn't generated
an error yet. If I find out why then I'll let you know. If anyone else can
explain in detail then I'd be interested to find out!

Sean.

 

[JR_06062005]

Thanks, that worked

Hi JR (OK to call you that?),

I haven't yet worked out exactly why it behaves this way - I found the same
thing - but I believe this shoul work, it solved it for me.


Sub LineFormatAtPageBreak()
Dim oHPgbr As HPageBreak
Debug.Print ActiveSheet.HPageBreaks.Count
ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).Select
ActiveSheet.Cells(1, 1).Select
For Each oHPgbr In ActiveSheet.HPageBreaks
MsgBox "Other row page break at: " & oHPgbr.Location.Address
' Format row at page break
Next
End Sub

I have used the Debug line to force the pagebreaks to show before the For
Each line. Anything that forces these to appear will do. Then I have selected
the last cell in column 1, before returning to the top. It appears that once
a cell has been selected below the final HPageBreak the problem goes. So far
I have tested a few times with different scenarios and this hasn't generated
an error yet. If I find out why then I'll let you know. If anyone else can
explain in detail then I'd be interested to find out!

Sean.