Is there always a blank row between the groupings?
If so, you can do something like this:



Option Explicit

Sub pgbrks()
Dim nrows As Long
Dim x
Dim firstAddress
Dim Number
Dim i As Integer
Dim rRow()
On Error Resume Next
nrows = ActiveSheet.UsedRange.Rows.Count
ReDim rRow(nrows)
Application.Calculation = xlCalculationManual
With ActiveSheet.Range("C1:C" & nrows)
Set x = .Find(What:="", LookIn:=xlFormulas, LookAt:=xlWhole)
If Not x Is Nothing Then
firstAddress = x.Address
Do
Number = Number + 1
rRow(Number) = x.Row
Set x = .FindNext(x)
Loop While Not x Is Nothing And x.Address < firstAddress
End If
End With
For i = Number To 1 Step -1
Range("C" & rRow(i - 1)).EntireRow.Select
Selection.End(xlToLeft).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

You can change where the page break is by changing Befo=ActiveCell
to where ever you want the break to occur.

It's a place to start, but 2 things not addressed are keeping the
groups together even if they fall at the end of a page? Didn't quite
understand what you were after there...

This works to find all the empty rows in the selection. With the
exception of the last one. I haven't got that figured out yet, but
thought someone might be able to help with that, as I need that myself.
Parts of this code were found here on this group, by looking for
"delete rows based on value of cells". I just modified it to add a
pagebreak instead of deleting the row.


Rob



On Jan 24, 12:35 pm, liz25mc
wrote:
Hello!
I've read and reread the page break posts and can't find what I need.
I have a spreadsheet list of items that are split into groups ie:

Column A Column B Column C
5 1234-566 Brown
5 Brown
5 Brown
Then a blank row
5 1235-555 Green
5 Green
5 Green
Then a blank row etc.

There might not always be three rows before the blank. I need a macro that
will add a page break but not break up the set of three if it falls at the
end of the sheet. I am thinking the page break will need to be based on
Column C, somehow, but I am not sure how to go about it. Any help would be
appreciated.
Thanks.

yeah, what rob said....................


On Jan 24, 2:24 pm, "okrob" wrote:
Is there always a blank row between the groupings?
If so, you can do something like this:

Option Explicit

Sub pgbrks()
Dim nrows As Long
Dim x
Dim firstAddress
Dim Number
Dim i As Integer
Dim rRow()
On Error Resume Next
nrows = ActiveSheet.UsedRange.Rows.Count
ReDim rRow(nrows)
Application.Calculation = xlCalculationManual
With ActiveSheet.Range("C1:C" & nrows)
Set x = .Find(What:="", LookIn:=xlFormulas, LookAt:=xlWhole)
If Not x Is Nothing Then
firstAddress = x.Address
Do
Number = Number + 1
rRow(Number) = x.Row
Set x = .FindNext(x)
Loop While Not x Is Nothing And x.Address < firstAddress
End If
End With
For i = Number To 1 Step -1
Range("C" & rRow(i - 1)).EntireRow.Select
Selection.End(xlToLeft).Select
ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=ActiveCell
Next i
Application.Calculation = xlCalculationAutomatic
End Sub

You can change where the page break is by changing Befo=ActiveCell
to where ever you want the break to occur.

It's a place to start, but 2 things not addressed are keeping the
groups together even if they fall at the end of a page? Didn't quite
understand what you were after there...

This works to find all the empty rows in the selection. With the
exception of the last one. I haven't got that figured out yet, but
thought someone might be able to help with that, as I need that myself.
Parts of this code were found here on this group, by looking for
"delete rows based on value of cells". I just modified it to add a
pagebreak instead of deleting the row.

Rob

On Jan 24, 12:35 pm, liz25mc
wrote:



Hello!
I've read and reread the page break posts and can't find what I need.
I have a spreadsheet list of items that are split into groups ie:

Column A Column B Column C
5 1234-566 Brown
5 Brown
5 Brown
Then a blank row
5 1235-555 Green
5 Green
5 Green
Then a blank row etc.

There might not always be three rows before the blank. I need a macro that
will add a page break but not break up the set of three if it falls at the
end of the sheet. I am thinking the page break will need to be based on
Column C, somehow, but I am not sure how to go about it. Any help would be
appreciated.
Thanks.- Hide quoted text -- Show quoted text -