Automatically Entering Page Breaks in an Excel sheet

Copper Contributor

Hi. Really need to know how to automatically enter page breaks in an excel sheet by condition. Specifically, I am a teacher, and have a list of my students per class. At the end of each class is a blank row. I would like to easily add a page break after each class so I can print them on a separate sheet of paper.


The problem I am having is that at the beginning of each class is a merged 3-columns with class information.  Below that are 3 unmerged columns. I believe the merged 3-columns are messing things up.


Thank you.

4 Replies

@NikolinoDE Thanks for your suggestions. That said, I know how to do that. What I'm looking for is a way to have new page breaks entered automatically at every empty row in a sheet. I believe it might be called conditional formatting.


As far as I know, you can't do this with conditional formatting.

Anyway, let me but love to disabuse.
Since the digital environment (Excel version, operating system, storage medium, etc.) was not specified, I recommend using VBA code with reservations.

Attached is an example code with empty rows.


Sub test()
BreakOnBlankLine range("A1:D188")
End Sub

Sub BreakOnBlankLine(bereich As range)
Dim anzSpalten%, anzLeerSpalten%, anzZeilen&, firstRow&, x&
anzSpalten = bereich.Columns.Count
anzZeilen = bereich.Rows.Count
firstRow = ActiveCell.Row


For x = firstRow To anzZeilen
    If WorksheetFunction.CountBlank(range(Cells(x, ActiveCell.Column), Cells(x, anzSpalten))) = anzSpalten Then
        Cells(x, 1).Select
        ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
    End If

End Sub



Hope I could help you with these information / VBA Code.



I know I don't know anything (Socrates)