A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old July 14th 06, 04:51 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default 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  
Old July 14th 06, 07:01 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,911
Default 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 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  
Old July 14th 06, 07:03 PM posted to microsoft.public.excel.worksheet.functions
Debra Dalgleish
external usenet poster
 
Posts: 2,979
Default 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  
Old July 14th 06, 07:20 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 22,911
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 12:43 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.