Make automatic page breaks ignore split cells in VBA in Excel

Question

Is it possible to generate automatic page breaks in VBA in Excel 2007 which ignore split cells (after merging adjacent cells)?

For instance, in the example below I would like the page break to be above row 51, not cutting through the middle of the merged cell A51-A53.

The code I have tried is:

workSheet.Range("A2:A" & bottomRow).PageBreak = xlPageBreakAutomatic

By only including column A in the range, I was hoping that it wouldn't generate a page break cutting through cells in this column.

Docmarti Docmarti · Accepted Answer · 2018-01-24T01:32:10

Try this code.

Sub HPageBreaks_and_MergeCells()
Dim sh As Worksheet
Dim NextPageBreakNumber As Long
Dim PageBreakFirstLine  As Object
Dim LineNumber As Long
Set sh = ThisWorkbook.ActiveSheet
ActiveWindow.View = xlPageBreakPreview
sh.ResetAllPageBreaks
NextPageBreakNumber = 1
While NextPageBreakNumber <= sh.HPageBreaks.Count
    Set PageBreakFirstLine = sh.HPageBreaks(NextPageBreakNumber).Location
    LineNumber = PageBreakFirstLine.Row
    If sh.Cells(LineNumber, 1).MergeCells = True Then
        Set sh.HPageBreaks(NextPageBreakNumber).Location = sh.Cells(sh.Cells(LineNumber, 1).MergeArea.Row, 1)
    End If
    NextPageBreakNumber = NextPageBreakNumber + 1
Wend
ActiveWindow.View = xlNormalView
End Sub

Make automatic page breaks ignore split cells in VBA in Excel

1 Answers