Excel Page Break set to specific worksheets-CodePudding

I'm just wondering if MS Excel can apply PAGE BREAK to specific worksheets at the same time. I have a number of sheets that all need to be printed with the same settings. However, there are some rows that are hidden and cannot be deleted.

Example:

Page 1 = Row 1 to Row 30

Page 2 = Rows 31-68 are hidden, as are Rows 39-48.

Page 3 = Rows 68-130 are hidden, as are Rows 75-89.

It should be noted that the rows can be changed.

PARTIAL SOLUTION:

The code below breaks the page per 28 rows. However, the hidden rows are still counted. How can I not include the hidden rows?

Sub Insert_PageBreaks()
Dim Lastrow As Long
Dim Row_Index As Long
Dim RW As Long

'How many rows do you want between each page break
RW = 28
With ActiveSheet
'Remove all PageBreaks
.ResetAllPageBreaks
'Search for the last row with data in Column A
Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
For Row_Index = RW   1 To Lastrow Step RW
.HPageBreaks.Add Before:=.Cells(Row_Index, 1)
Next
End With
End Sub

CodePudding user response：

You could keep a counter of how many rows are visible while looping.

Sub Insert_PageBreaks()
    Dim Lastrow As Long
    Dim RowIndex As Long
    Dim RW As Long

    'How many rows do you want between each page break
    RW = 28
    With ActiveSheet
        'Remove all PageBreaks
        .ResetAllPageBreaks
        'Search for the last row with data in Column A
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        For RowIndex = 1 To Lastrow
            
            If .Rows(RowIndex).Hidden = False Then
                Dim VisibleRowsCounter As Long
                VisibleRowsCounter = VisibleRowsCounter   1
            End If
            
            If VisibleRowsCounter = RW Then
                .HPageBreaks.Add Before:=.Cells(RowIndex, 1)
                VisibleRowsCounter = 0 'Reset
            End If
            
        Next
    End With
End Sub