Loop for Page Break

[Bruce Roberson]

Lets say I had a block of 500 contiguous rows of data, and
I wanted to place my cursor in the top left most cell.
Then I wanted the macro to first determine how many rows
there were. Then, I wanted the macro to do a page break
for each line in the block and of course break out of the
loop at the end of the block.


Thanks,


Bruce

 

[Nigel]

Bruce,
One way .....
The first part determines the range (rng)of data in column 1 from row 1 to
row n.
The second part adds a page break before each row.
Hope this gives you a template to work with.
Cheers
Nigel

Sub PageBreaker()
Dim i As Integer
Dim rng As Range
Dim cell As Variant

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

For Each cell In rng
Rows(cell + 1).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Next cell
End Sub

 

[Bruce Roberson]

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

In this line, is the range beginning at cell A1? How would I change that to
where it started at the active cell, which probably is on down the sheet a
little ways. Because I know there will be other information both above and
below the block in question.

Thanks,


Bruce

Bruce,
One way .....
The first part determines the range (rng)of data in column 1 from row 1 to
row n.
The second part adds a page break before each row.
Hope this gives you a template to work with.
Cheers
Nigel

Sub PageBreaker()
Dim i As Integer
Dim rng As Range
Dim cell As Variant

Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

For Each cell In rng
Rows(cell + 1).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
Next cell
End Sub






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---

 

[Jim Cone]

Bruce,

Give this one a try.
There are some notes as to what is going on included in the code.
Note: Setting manual page breaks is a slow operation.

"--------------------------------------
'Jim Cone March 16, 2004
Sub PageBreaksEveryRow()
Dim objRng As Range
Dim objCell As Range

Application.ScreenUpdating = False
'Select more than 2 cells and the program uses the selection.
If Selection.Rows.Count > 2 Then
Set objRng = Selection.Columns(1)
Else
' or Program will use contiguous data above and below active cell.
Set objRng = Application.Intersect(ActiveCell.CurrentRegion,
ActiveCell.EntireColumn)
If objRng.Rows.Count < 3 Then
MsgBox "Not enough rows ", vbInformation, " Bruce did it"
GoTo CleanUp
Exit Sub
End If
End If
'Excel limits the number of page breaks it will add.
If objRng.Count > 1024 Then Set objRng = objRng.Resize(1024, 1)
'Page breaks are set Above the specified cell, row 1 will error out.
'So move the whole thing down one row. No error trapping is done
' to catch a range extending below last spreadsheet row.
Set objRng = objRng.Offset(1, 0).Resize(objRng.Rows.Count - 1, 1)
For Each objCell In objRng.Cells
ActiveSheet.DisplayPageBreaks = False
objCell.EntireRow.PageBreak = xlPageBreakManual
Next 'lngindex
Application.ScreenUpdating = True

CleanUp:
Set objRng = Nothing
Set objCell = Nothing
End Sub
'--------------------------------------

Regards,
Jim Cone
San Francisco, CA