Page Breaks, and Inserting Blank Rows

Page Breaks on Column A, Inserting Blank Rows on change in Column B

When you insert or delete lines you should start from the bottom row of those you examine --- from the last cell row upward. For merely inserting page breaks it wouldn't matter which direction you went.

Example coding

Insert Page Breaks on change in Column A, and insert blank rows on change in Column B.  To prevent inserting still more lines when rerunning a check is made to see if a blank row had already been inserted -- so this can be rerun without further changes.

If you want a new sheet activate the appropriate lines in the example coding.

Sub PageBreakonA_SepB()
'David McRitchie, misc, 2001-05-02
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'--Activate if you want changes only in a copy
'Sheets(ActiveSheet.Name).Copy After:=Sheets(ActiveSheet.Name)
Dim lastrow As Long
Dim lastcell As Range
Dim lastMajor As String
Dim lastSecond As String
Dim blanks As Long
blanks = 0
Dim iRow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastMajor = Cells(lastrow, 1)
lastSecond = Cells(lastrow, 2)
Cells.PageBreak = xlNone
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For iRow = lastrow To 3 Step -1
  If Trim(Cells(iRow, 1)) <> "" Then
    If Cells(iRow, 1) <> lastMajor Then
        Cells(iRow + 1, 1).PageBreak = xlManual
        lastMajor = Cells(iRow, 1)
        lastSecond = Cells(iRow, 2)
        blanks = 0
    ElseIf Cells(iRow, 2) <> lastSecond Then
        lastSecond = Cells(iRow, 2)
        If Cells(iRow + 1, 1) <> "" Then
            'don't insert if already had a blank line
            Cells(iRow + 1, 1).EntireRow.Insert
        End If
    End If
  End If
Next iRow
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

If you don't like the results (and sorting is okay):
Manually Remove All Page Breaks:
    Select all cells (Ctrl+A)
    Insert menu --> Reset all Page Breaks
Manually Remove Inserted Lines:
    Select all Cells (Ctrl+A)
    Sort on Column A and B, to place blank lines at bottom
       -- note lower left corner whether you have header rows or not
    Delete those blank lines, and save
    To reestablish the last cell (Ctrl+End).

Related

• Calculating Top Margin (TM)
• Logo at top (logoshd) of first page only or entire report.
• Page Breaks (pagebreaks.htm), and Page Breaks, and Inserting Blank Rows (pagebrks.htm)
• Pathname in Headers, Footers, and cells
• Repeating column and row heading information (headcols) in printed report (page setup) or on screen (Window/Freeze Panes).
• Slow Response, particularly topic:  Page Setup and speed.
• Q199505 macro may take several minutes if page breaks are visible.
   
• Posted 2001-05-02 thread news:OJBna1w0AHA.1416@tkmsftngp03
• Insert Rows
• Snake Columns

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved