Excel Page Break depending on row values

[sagobogger]

Hi folks, back for some more of your great help!

A friend is asking me how to automatically page break a very many row Excel spreadsheet when the value in a column changes. e.g. In the following example page break after the last Cat, last Dog, Otter, Zebra.

Cat
Cat
Cat
Dog
Dog
Otter
Zebra

Is there any conditional formatting way to do that, or what other suggestions does anyone have to do this?

TIA

 

[SkipVought]

Hi,

Check out Data > Subtotals with the page break option.



Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[yogia]

Hi sagobogger:

Assuming your data starts in cell A1, how about using the following VBA code ...

Range("a2").Select
    Do While ActiveCell.Value <> ""
        If ActiveCell.Value <> ActiveCell(0, 1) Then
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
        End If
        ActiveCell(2, 1).Select
    Loop

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Hey Yogia,

This is the Office Forum - native application solutions. VBA in Forum707.

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[yogia]

Sorry ... of course, you are right Skip!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Yet another OP, AWOL???

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]