Adjust horizontal pagebreaks in page setup

[YasserKhalil]

Hello everyone
In the attachment, there are a lot of settings done by code as for page setup using such a code

Code: Select all

        Application.PrintCommunication = False
        With .PageSetup
            .PrintTitleRows = "$1:$1"
            .LeftMargin = Application.InchesToPoints(0)
            .RightMargin = Application.InchesToPoints(0)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .CenterHorizontally = True
            .FitToPagesWide = 1
            .FitToPagesTall = 0
        End With
        Application.PrintCommunication = True

When previewing the worksheet, there are some items separated in two pages and this is not desired.
For example, item 12 in the worksheet is at page 1 and page 2. How can I control this by code to make the item either in page1 or if it is not possible to make it in page2
Simply the item should be in one page, not separated in two pages

Posted here too
https://www.excelforum.com/excel-progra ... setup.html

[YasserKhalil]

This is my try till now

Code: Select all

Sub Test()
    Dim x As Long, iRow As Long
    With ActiveSheet
        For x = 1 To .HPageBreaks.Count
            iRow = .HPageBreaks(x).Location.Row
            If Cells(iRow, 1).MergeArea.Cells(1).Row < iRow Then
                'Debug.Print iRow
                '.Rows(Cells(iRow, 1).MergeArea.Cells(1).Row).PageBreak = xlPageBreakManual
                'Need to move the page break to Cells(iRow, 1).MergeArea.Cells(1).Row
            End If
        Next x
    End With
End Sub

[HansV]

Does this work?

Code: Select all

Sub Test2()
    Dim x As Long, iRow As Long
    With ActiveSheet
        For x = 1 To .HPageBreaks.Count
            iRow = .HPageBreaks(x).Location.Row
            If Cells(iRow, 1).MergeArea.Cells(1).Row < iRow Then
                .HPageBreaks.Add Before:=Cells(iRow, 1).MergeArea.Cells(1)
            End If
        Next x
    End With
End Sub

[YasserKhalil]

Amazing my tutor. Thank you very much for your great support.

[YasserKhalil]

Sometimes because of the code and the merging cells, there is an empty area on the page. Is it possible to change the row height of such a page so as to fill this empty area?