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

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