Avoiding page breaks across merged cells - Code not working as expected
 

Hi All,

I am trying to set up the horizontal page breaks in a worksheet
automatically, so that they never break across a merged cell.

The merged cells are in column C.

The code I have written is below.

It correctly identifies where the first page break occurs, but it
won't actually change the location of that break.

Upon testing, in my immediate window, with the first (and only)
horizontal break at A85, I get a repeating list as follows:

BreakCell = $A$85
NewBreak = $A$86
MyBreak = $A$85

BreakCell = $A$85
NewBreak = $A$86
MyBreak = $A$85

..... and so on forever looping

The issue therefore appears to be that the code is failing to actually
change the horizontal break location. The location property help
explicitly states that it is read / write, but I am not sure what to
do from here.

Any suggestions are appreicated.

Thanks,

Alan.


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Option Explicit


Sub FixHBreaks()

Dim MyHBreak As HPageBreak
Dim BreakCell As Range
Dim ReRun As Boolean
Dim NewBreak As Range


ActiveSheet.Unprotect


Do

ReRun = False

For Each MyHBreak In ActiveSheet.HPageBreaks

Set BreakCell = MyHBreak.Location

Debug.Print "BreakCell = " & BreakCell.Address

If BreakCell.Offset(0, 2).MergeArea.Rows.Count 1 Then

Set NewBreak = MyHBreak.Location.Offset(1, 0)

' This next line appears to be inneffective

MyHBreak.Location = NewBreak

' These next three lines generate the info in the immediate window

Debug.Print "NewBreak = " & NewBreak.Address
Debug.Print "MyBreak = " & MyHBreak.Location.Address
Debug.Print

ReRun = True

End If

Next


Loop Until ReRun = False


End Sub

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Avoiding page breaks across merged cells - Code not working as expected
 

You can't set the pagebreak beyond the default/automatic pagebreak. The
reason you have a default/automatic pagebreak is because the hardware
limitations on the printer demand a pagebreak at that location. You can
insert a pagebreak before it, but anything set after it will not affect it.

--
Regards,
Tom Ogilvy

"Alan" wrote in message
...

Hi All,

I am trying to set up the horizontal page breaks in a worksheet
automatically, so that they never break across a merged cell.

The merged cells are in column C.

The code I have written is below.

It correctly identifies where the first page break occurs, but it
won't actually change the location of that break.

Upon testing, in my immediate window, with the first (and only)
horizontal break at A85, I get a repeating list as follows:

BreakCell = $A$85
NewBreak = $A$86
MyBreak = $A$85

BreakCell = $A$85
NewBreak = $A$86
MyBreak = $A$85

.... and so on forever looping

The issue therefore appears to be that the code is failing to actually
change the horizontal break location. The location property help
explicitly states that it is read / write, but I am not sure what to
do from here.

Any suggestions are appreicated.

Thanks,

Alan.


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Option Explicit


Sub FixHBreaks()

Dim MyHBreak As HPageBreak
Dim BreakCell As Range
Dim ReRun As Boolean
Dim NewBreak As Range


ActiveSheet.Unprotect


Do

ReRun = False

For Each MyHBreak In ActiveSheet.HPageBreaks

Set BreakCell = MyHBreak.Location

Debug.Print "BreakCell = " & BreakCell.Address

If BreakCell.Offset(0, 2).MergeArea.Rows.Count 1 Then

Set NewBreak = MyHBreak.Location.Offset(1, 0)

' This next line appears to be inneffective

MyHBreak.Location = NewBreak

' These next three lines generate the info in the immediate window

Debug.Print "NewBreak = " & NewBreak.Address
Debug.Print "MyBreak = " & MyHBreak.Location.Address
Debug.Print

ReRun = True

End If

Next


Loop Until ReRun = False


End Sub

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Avoiding page breaks across merged cells - Code not working as expected
 

"Tom Ogilvy" wrote in message
...

You can't set the pagebreak beyond the default/automatic pagebreak.
The reason you have a default/automatic pagebreak is because the
hardware limitations on the printer demand a pagebreak at that
location. You can insert a pagebreak before it, but anything set
after it will not affect it.


Thanks Tom - obvious now you point it out!

Regards,

Alan.