+ Reply to Thread
Results 1 to 4 of 4

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

  1. #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.


  2. #2
    Gord Dibben
    Guest

    Re: 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, [email protected] 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. #3
    Debra Dalgleish
    Guest

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

    [email protected] 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. #4
    Gord Dibben
    Guest

    Re: 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 <[email protected]>
    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
    >
    >[email protected] 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1