View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gord Dibben Gord Dibben is offline
external usenet poster
 
Posts: 22,906
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 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