A
Alan
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
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
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
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+