Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unable to delete page break on Excel spreadsheet | Excel Worksheet Functions | |||
Subtotal page break error? | Excel Worksheet Functions | |||
How do I remove the page number from a page break preview and kee. | Excel Discussion (Misc queries) | |||
AUTO INSERT PAGE BREAK FOR EACH COPIED HEADING | Excel Discussion (Misc queries) | |||
How do I change default settings to page break preview | Setting up and Configuration of Excel |