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 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.

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
        Gold 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