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


--
jim.casagrande
------------------------------------------------------------------------
jim.casagrande's Profile: http://www.excelforum.com/member.php...o&userid=32987
View this thread: http://www.excelforum.com/showthread...hreadid=528156

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?

--
Ken Hudson


"jim.casagrande" wrote:


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


--
jim.casagrande
------------------------------------------------------------------------
jim.casagrande's Profile: http://www.excelforum.com/member.php...o&userid=32987
View this thread: http://www.excelforum.com/showthread...hreadid=528156

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 Befo=ActiveCell
CountBlanks = 0
End If
End If
Next Iloop

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


--
Ken Hudson


"jim.casagrande" wrote:


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

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


--
jim.casagrande
------------------------------------------------------------------------
jim.casagrande's Profile: http://www.excelforum.com/member.php...o&userid=32987
View this thread: http://www.excelforum.com/showthread...hreadid=528156

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


"jim.casagrande" wrote:


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

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


--
jim.casagrande
------------------------------------------------------------------------
jim.casagrande's Profile: http://www.excelforum.com/member.php...o&userid=32987
View this thread: http://www.excelforum.com/showthread...hreadid=528156