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.
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
Blue color is used to simulate page breaks.
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).
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