Conditional Page Break

M

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,
 
P

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
 
M

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.
 
M

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


Per Jessen said:
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
 
P

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
Mike Saffer said:
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.
 
M

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.
 
P

Per Jessen

Hi Mike

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

Regards,
Per
Copenhagen, Denmark
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top