Creating conditional page breaks within Excel 2

[mackinmn]

Maybe there is a better way of wording this, but I'm shooting from the hip so please bear with me.

I have a very large .xls file that I wish to print. The sheet is sorted by the contents of column A. Whenever the fifth (5th) character of the string in column A changes, I want a page break to occur. Unfortunately, there is no fixed number of rows I can base my page break on.

I have scoured the Net using the keywords "Excel", "conditional", and "page break", but have yet to find any topic that resembles what I am trying to accomplish. I find it hard to believe that no one out there has ever had a giant (700+ page) spreadsheet that they wished to print using conditional page breaks. Manually inserting the page breaks would be more work than it's worth, so I am asking for any input that will help me put the page breaks where they belong.

Thanks in advance,
John

p.s. Excel 2000 is what I am using.

 

[JVFriederick]

Would you be willing to write a formula in another column that has just the 5th character ?

If yes, I can provide a quick solution. (Probably)

 

[mackinmn]

I guess I might have been willing to, but instead I ran across the solution I've been seeking.

If you or anyone else is interested, I did it by placing a transparent autoshape rectangle onto the sheet and assigning to it a macro named AutoBreak with the following script:

Sub AutoBreak()
Set Urange = ActiveSheet.UsedRange
Set ColA = Range("A:A&quot
Set ARange = Intersect (ColA, URange)
Set BRange = ARange.Offset(1,0).Resize(ARange.Rows.Count -1)
Cells.PageBreak = xlNone
For Each cell In Brange
If Mid(cell.Value, 5, 1) <> Mid(cell.Offset(1,0).Value, 5, 1) Then
cell.Offset(1,0).EntireRow.PageBreak = xlPageBreakManual
End If
Next
End Sub


*Text in red is optional. This is where the user specifies the exact character(s) to be compared for the pagebreak condition to be met. If omitted, the entire cell will be compared.

This is a very useful macro for anyone printing a large report requiring conditional page breaks.

p.s. Thanks for your offer to help.

 

[JVFriederick]

That looks pretty cool. I planned to use a column with the 5th digit code, then use the Filter feature in Excel. The VB code would step through the various codes, pass the code parameter on to the filter, and print the current view.