Page Breaks, and Inserting Blank Rows

Home page:
[View without Frames]

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
1Col-ACol-B Col-CCol-DCol-E
2Item 1B2C2D2 E2
3Item 1B2C3D3 E3
4Item 1B4C4D4 E4
5Item 1B4C5D5 E5
6Item 1B4C6D6 E6
7Item 1B7C7D7 E7
8Item 2B8C8D8 E8
9Item 2B8C9D9 E9
10Item 2B8C10 D10E10
11Item 2B8C11 D11E11
12Item 2B12C12 D12E12
13Item 3B13C13 D13E13
14Item 3B14C14 D14E14
15Item 3B15C15 D15E15
16Item 4B16C16 D16E16
17Item 4B17C17 D17E17

Blue color is used to simulate page breaks.

1Col-A Col-B  Col-C Col-D Col-E 
2Item 1B2C2D2 E2
3Item 1B2C3D3 E3
5Item 1B4C4D4 E4
6Item 1B4C5D5 E5
7Item 1B4C6D6 E6
9Item 1B7C7D7 E7

10Item 2B8C8>D8 E8
11Item 2B8C9 D9E9
12Item 2B8C10 D10E10
13Item 2B8C11 D11E11
15Item 2B12C12 D12E12

16 Item 3B13C13D13E13
18Item 3B14C14 D14E14
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).


You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on May 2, 2001.

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