Sub Make_Pages_Breack() Dim rUsRng As Range, li As Long, lCnt As Long Set rUsRng = Range("A1", Cells.SpecialCells(11)) For li = 1 To rUsRng.Rows.Count If rUsRng.Rows(li).PageBreak <> xlNone Then If rUsRng.Cells(li, 1).MergeCells Then lCnt = li - Cells(li, 1).MergeArea.Row If lCnt > 0 Then Rows(li - lCnt).Resize(lCnt).Insert: lCnt = 0 End If End If Next liEnd Sub
Application.ScreenUpdating = FalseApplication.Calculation = xlCalculationManualApplication.EnableEvents = False
Private Sub()Dim sh As WorksheetDim NextPageBreakNumber As LongDim PageBreakFirstLine As ObjectDim LineNumber As LongSet sh = ThisWorkbook.ActiveSheetActiveWindow.View = xlPageBreakPreviewsh.ResetAllPageBreaksNextPageBreakNumber = 1While 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 + 1WendEnd Sub