Page Breaks

[jim.casagrande]

I have a set of nearly identical worksheets that contain upwards of
2,000 rows containing information on mergers & acquisitions. Each deal
contains 3 or 4 rows of data, seperated by a blank row in between. 90%
of these deals take up 3 rows. the other 10% take up 4 rows. What I
have to do now, manually, is set page breaks every 12 deals.

I tried to record a macro by starting at B11 (the first blank cell
below the header) and using Ctrl + the down arrow 24 times to capture
12 deals (whether they are 3 or 4 rows), then hitting the down arrow
once more to be below the 12th deal, then left arrow once to highlight
column A, then alt, I, B to set the page break.

This works for the most part, except when that pattern leaves the
cursor right below a page break that is already there. Then the alt,
I, B removes the page break.

I know there is a way to clear page breaks, but i'm not sure how to do
it. Also, is there an easier way to program this in VBA? If i was
inserting a page break every 36 lines it'd be easy, but the number of
rows varies.

Any input would be greatly appreciated.

Thanks,
--Jim

 

[Guest]

Hi Jim,
Need a litle more information, please.
Is there a column from which we can determine when a deal changes? In other
words, does column A look like this:

Deal1
Deal1
Deal1

Deal3
Deal3
Deal3
Deal3

........

And does this column have one or more headers rows?

If the above is correct, do you want to insert a page break after every 12
deals, not mattering whether there are three or four rows per deal?

 

[jim.casagrande]

Sorry, should've laid out the pages a bit better...

Header
Column A Column B Column C -> end
*blank row
Date1 Acquiror 100 90
72 5.8
Date2 Target
Detail1 Business
*blank row
Date1 Acquiror 300 220
104 10.2
Date2 Target
Detail1 Business
Comment1
*blank row

above i laid out two deals with the header above.
The difference between the two is the "Comment1". When there is
"Comment1", each "Deal" takes up 4 rows, otherwise they are three row
each.

Let me know if anymore clarification is necessary

 

[Guest]

Okay, we're getting closer....
The trick is to describe the logic we need to build into the macro to tell
it when to insert a page break.
Can we say that we would start in the first date field in column A. We would
count down until we counted 12 blank rows after that. Then we would insert a
page break.
Then count 12 more blank rows, insert a page break, etc.

 

[jim.casagrande]

Yes, I agree with what you said, although we would want to count 1
blank rows in column B (Column A will always be 3 rows, Column B varie
between 3 and 4).

All of these pages have headers that take up rows 1 through 10, so
think the starting cell is B11. B12 always has data and is th
Acquiror for the very first deal

 

[Guest]

Jim,

Make a back-up copy of your workbook.
Press Ctrl and F11 to open the Visual Basic Editor.
Select Insert and then Module.
Copy the code below and paste it into the module.
Close the Editor.
Go to Tools > Macro > Macros…
Highlight the macro and click Run.

--------------------
Option Explicit
Public Sub PageBreaks()

Dim Anchor As Integer
Dim Iloop As Double
Dim NumRows As Double
Dim CountBlanks As Integer

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

CountBlanks = 0
NumRows = Range("A65536").End(xlUp).Row
For Iloop = 1 To NumRows
If IsDate(Cells(Iloop, 1)) Then
Anchor = Iloop
Exit For
End If
Next Iloop

For Iloop = Anchor To NumRows
If IsEmpty(Cells(Iloop, 1)) Then
CountBlanks = CountBlanks + 1
If CountBlanks = 12 Then
Rows(Iloop).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
CountBlanks = 0
End If
End If
Next Iloop

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Guest]

SpecialCells could also be useful for this type of operation. Depending on
where you want the break (above the blank row or below it) change Cells(x.Row
+ 1, 1) - which places the break below the blank row to Cells(x.Row, 1) to
break above the blank row.


Sub test()
Dim Rng As Range
Dim x As Range
Dim count As Long

Set Rng = Range("B1", Cells(Rows.count, _
2).End(xlUp)).SpecialCells(xlCellTypeBlanks)

For Each x In Rng.Cells
count = count + 1
If count Mod 12 = 0 Then _
Cells(x.Row + 1, 1).PageBreak = xlPageBreakManual
Next x

End Sub