ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   insert page break when there is a change in the row value (https://www.excelbanter.com/excel-worksheet-functions/99354-insert-page-break-when-there-change-row-value.html)

[email protected]

insert page break when there is a change in the row value
 
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

insert page break when there is a change in the row value
 
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 InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

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


Gord Dibben MS Excel MVP


On 14 Jul 2006 08:51:01 -0700, wrote:

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

insert page break when there is a change in the row value
 
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 DataSubtotals
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

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



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html


Gord Dibben

insert page break when there is a change in the row value
 
Thanks for posting Debra.

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


Gord

On Fri, 14 Jul 2006 14:03:37 -0400, Debra Dalgleish
wrote:

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

wrote:
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


All times are GMT +1. The time now is 07:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com