insert page break when there is a change in the row value

[mandy_ball]

I am trying to find a formula to enter a page break when there is a
change in the information in a column. For example, I have an address
book in an excel spreadsheet and I want to insert a page break at the
end of the a's, b's c's and so on.

Thanks.

 

[Gord Dibben]

Mandy

Insertion of page breaks will have to be done through VBA, not worksheet
functions/formulas.

Sub rowchange()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
FirstRow = 2
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1
If Mid(Cells(iRow, "a").Value, 1, 1) <> _
Mid(Cells(iRow - 1, "a").Value, 1, 1) Then
Rows(iRow).PageBreak = xlPageBreakManual
End If
Next
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo


Gord Dibben MS Excel MVP

I am trying to find a formula to enter a page break when there is a
change in the information in a column. For example, I have an address
book in an excel spreadsheet and I want to insert a page break at the
end of the a's, b's c's and so on.

Thanks.

Gord Dibben MS Excel MVP

 

[Debra Dalgleish]

You could use a LEFT formula, to calculate the first letter, e.g.:

=LEFT(B2,1)

Copy that formula down to the last row of data.
Sort the list alphabetically

Then, use the Subtotal feature to add page breaks:
Select a cell in the table
Choose Data>Subtotals
From the 'At each change in' dropdown, choose the column of first letters
Use the function Count
Under 'Add subtotal to' select the column of first letters
Add a check mark to 'Page break between groups'
Remove the check mark from 'Subtotal below data'
Click OK

 

[Gord Dibben]

Thanks for posting Debra.

Learn something new every day, or in my case, hourly.


Gord

You could use a LEFT formula, to calculate the first letter, e.g.:

=LEFT(B2,1)

Copy that formula down to the last row of data.
Sort the list alphabetically

Then, use the Subtotal feature to add page breaks:
Select a cell in the table
Choose Data>Subtotals
From the 'At each change in' dropdown, choose the column of first letters
Use the function Count
Under 'Add subtotal to' select the column of first letters
Add a check mark to 'Page break between groups'
Remove the check mark from 'Subtotal below data'
Click OK

Gord Dibben MS Excel MVP