Conditional Page Break

[Mike Saffer]

Hello everyone,
I not sure if there is such a thing but here goes.

I'd like a page break each time the first 4 characters of a value changes in
Column A. To make my problem even harder for me is that there are blank
cells in Column A. Finally, to make my problem impossible for me is that the
page breaks would have to skip the final blank cell in a group and break just
below it. It's difficult to explain but I hope the example below helps
clarify what I'm saying.

Here's what I have:
A
Starke
Starke
Starke
(blank cell)
St Augstine
St Augustine
(blank cell)
St Augustine 2
St Augustine 2
(blank cell)
St Johns
St Johns
(blank cell)

Here are the desired results
A
Starke
Starke
Starke
(blank cell) with page break just below
St Augstine
St Augustine
(blank cell)
St Augustine 2
St Augustine 2
(blank cell) with page break just below
St Johns
St Johns
(blank cell) with page break just below

St Augustine and St Augustine 2 are in the same group because the first 4
characters did not change. I have to keep the blank cells intact.

Can this be done with code? Many thanks for looking,

 

[ShaneDevenshire]

Hi Mike,

You will probably need to use VBA, is that an option for you?

 

[Per Jessen]

Hi

Try to see if this macro is what you want:

Sub test()
lastrow = Range("a65536").End(xlUp).Row
ActiveSheet.ResetAllPageBreaks

ActiveWindow.ActiveSheet.HPageBreaks.Add before:=Cells(lastrow + 2, "A")

Val1 = Left(Range("A1").Value, 4)
For r = 2 To lastrow
If Cells(r, "A").Value = "" Then
If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then
ActiveSheet.HPageBreaks.Add before:=Cells(r + 1, "A")
Val1 = Cells(r + 1, "A").Value
End If
End If
Next

End Sub

Regards,
Per

 

[Mike Saffer]

Hello Shane,

I'm somewhat familiar with the VB editor, creating simple modules, editing
macros I have recorded, etc. I am not experienced enough to actually write
the code myself for this one yet.

 

[Mike Saffer]

Hello Per,

Your macro did indeed put the page break after the blank cells separating
the groups. The only thing it did not do was keep the groups together.
After I ran your macro I got this:

Starke
Starke
Starke
(blank cell) with page break just below
St Augstine
St Augustine
(blank cell) with page break just below*****
St Augustine 2
St Augustine 2
(blank cell) with page break just below
St Johns
St Johns
(blank cell) with page break just below

***** Because the first 4 characters in St Augustine and St Augustine 2 are
the same (ST blank space A) I was hoping the page break would come after only
St Augustine 2's blank cell instead of after both St Augustine and St
Augustine 2's blank cells.

I'd like a page break each time the first 4 characters of a value changes
in Column A. Otherwise it "ungroups" the St Augustines and prints out way
too many pages.

Per, thank you for taking the time to look at my problem here.
--
Mike
Jacksonville, Florida

Hi

Try to see if this macro is what you want:

Sub test()
lastrow = Range("a65536").End(xlUp).Row
ActiveSheet.ResetAllPageBreaks

ActiveWindow.ActiveSheet.HPageBreaks.Add before:=Cells(lastrow + 2, "A")

Val1 = Left(Range("A1").Value, 4)
For r = 2 To lastrow
If Cells(r, "A").Value = "" Then
If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then
ActiveSheet.HPageBreaks.Add before:=Cells(r + 1, "A")
Val1 = Cells(r + 1, "A").Value
End If
End If
Next

End Sub

Regards,
Per

 

[Per Jessen]

Hi Mike

Thanks for your reply.

I just forgot a little detail to make it work as desired :-(

Try this:

Sub InsertPageBreaks()
lastrow = Range("a65536").End(xlUp).Row
ActiveSheet.ResetAllPageBreaks

ActiveWindow.ActiveSheet.HPageBreaks.Add before:=Cells(lastrow + 2, "A")

val1 = Left(Range("A1").Value, 4)
For r = 2 To lastrow
If Cells(r, "A").Value = "" Then
If Not Left(Cells(r + 1, "A").Value, 4) = val1 Then
ActiveSheet.HPageBreaks.Add before:=Cells(r + 1, "A")
val1 = Left(Cells(r + 1, "A").Value, 4)
End If
End If
Next
End Sub

Best regards,
Per

Hello Per,

Your macro did indeed put the page break after the blank cells separating
the groups. The only thing it did not do was keep the groups together.
After I ran your macro I got this:

Starke
Starke
Starke
(blank cell) with page break just below
St Augstine
St Augustine
(blank cell) with page break just below*****
St Augustine 2
St Augustine 2
(blank cell) with page break just below
St Johns
St Johns
(blank cell) with page break just below

***** Because the first 4 characters in St Augustine and St Augustine 2
are
the same (ST blank space A) I was hoping the page break would come after
only
St Augustine 2's blank cell instead of after both St Augustine and St
Augustine 2's blank cells.

I'd like a page break each time the first 4 characters of a value changes
in Column A. Otherwise it "ungroups" the St Augustines and prints out way
too many pages.

Per, thank you for taking the time to look at my problem here.

 

[Mike Saffer]

Outstanding job Per,

The macro worked and the page breaks are now where I needed them. I am much
obliged to you and this forum.

Thank you very much.

 

[Per Jessen]

Hi Mike

Thanks for your reply. I'm glad to help.

Regards,
Per
Copenhagen, Denmark