MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional PAGE Breaking

Posted by jim Spencer on April 19, 2001 11:44 AM

I have an Excel Spreadsheet of 5000 rows. I need a way to automajically insert page breaks for departments versus going to where each department ends and insert the page break manually.
columns A B C D Headings are:
Name, rank,serialnumber,department
When I encounter,a change in department, say on row 50 for Dept A, I want to insert a Page Break, the Next dept change is on Row 110, therefore insert Pagebreak. Is there a macro or visual basic code to do this?
I have created a formula say in column E :
=IF(D3<>D2,"Y","") This assist me in finding where the department changes are: Is there a special FIND and REPLACE macro to Find "Y" in Column E and Replace with "INSERT PAGE BREAK". Your help and assistance is appreciated.

Posted by Susan on April 19, 2001 12:20 PM

I handle it by using the Data, Subtotals command. It isn't all that fast, but can be done 'straight' or from inside a macro after data changes.