If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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. 
Ads 
#2




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. Rightclick 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 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




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




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 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 > 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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
unable to delete page break on Excel spreadsheet  sonjsull  Excel Worksheet Functions  1  June 3rd 06 07:16 PM 
Subtotal page break error?  Dips  Excel Worksheet Functions  0  May 9th 06 11:21 PM 
How do I remove the page number from a page break preview and kee.  shev82  Excel Discussion (Misc queries)  1  April 21st 06 10:30 AM 
AUTO INSERT PAGE BREAK FOR EACH COPIED HEADING  wil4d  Excel Discussion (Misc queries)  4  December 21st 05 04:02 AM 
How do I change default settings to page break preview  Jimbo693  Setting up and Configuration of Excel  1  April 1st 05 06:51 PM 