Forum Discussion

HarveyZ123's avatar
HarveyZ123
Copper Contributor
Jan 26, 2023

Automatically Entering Page Breaks in an Excel sheet

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 bl...
excel
HarveyZ123's avatar
HarveyZ123
Copper Contributor
Jan 29, 2023

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.

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 31, 2023

    HarveyZ123 

    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
bereich.Select
firstRow = ActiveCell.Row

ActiveSheet.ResetAllPageBreaks

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
Next

End Sub

     

     

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

     

    NikolinoDE

    I know I don't know anything (Socrates)

Resources