Stop page breaks in the middle of merged cells

D

dave

I have a macro that generates a report that has merged cells. Can I get the
page breaks not to split any merged cells?
 
D

Dave Peterson

I think you'll have to use manual page breaks and put them where you want them.
 
D

dave

Dave,
I figured out a way within a macro to force the page breaks to a specific
row. I want to use the same macro for worksheets with multiple pages and some
with only one page. The macro crashes if I set the page breaks for more rows
than there are data for. I found the following "If" statement for a different
page break problem. I don't fully understand all the coding. I there some
type of if statement that will set the the page breaks at a known location
only if there is data that many rows down?

If your data looks like this

east
east
east
west
west
west
etc
etc
etc

This macro will insert a pagebreak at each change of data column A

Sub InsertBreak_At_Change()
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub
 
D

Dave Peterson

I'm not sure why your macro breaks, but maybe you could pick out a column that
you can "group" by and use something like this:

Option Explicit
Sub testme()

Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

With Worksheets(1)
.ResetAllPageBreaks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'do nothing, same group
Else
.HPageBreaks.Add Before:=.Cells(iRow, "A")
End If
Next iRow
End With
End Sub

But this doesn't address any merged cells.




Dave,
I figured out a way within a macro to force the page breaks to a specific
row. I want to use the same macro for worksheets with multiple pages and some
with only one page. The macro crashes if I set the page breaks for more rows
than there are data for. I found the following "If" statement for a different
page break problem. I don't fully understand all the coding. I there some
type of if statement that will set the the page breaks at a known location
only if there is data that many rows down?

If your data looks like this

east
east
east
west
west
west
etc
etc
etc

This macro will insert a pagebreak at each change of data column A

Sub InsertBreak_At_Change()
Dim i As Long
For i = Selection.Rows.Count To 1 Step -1
If Selection(i).Row = 1 Then Exit Sub
If Selection(i) <> Selection(i - 1) And Not IsEmpty _
(Selection(i - 1)) Then
With Selection(i)
.PageBreak = xlPageBreakManual
End With
End If
Next
End Sub
Click to expand...
 
Top