VBA: Adding Conditional Page Breaks

If it is as stated above.

you could write something like this

COL = 3 ' what ever column you want to measure.
LastRw = ActiveSheet.UsedRange.Rows.Count

For x =1 to LastRw

if Cells(x,COL)<> cells(x-1,COL) then

ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell

end if

next

Some fine tuning may be required but it would do what you want.

You would have to replace the 3 with the desired column under test.

Hi DeZaStR,

See if you have any joy with the following:
<pre>Sub InsertPBs()
Dim rngMyRange As Range, rngCell As Range

With Worksheets("Sheet1")
Set rngMyRange = .Range(.Range("B1"), .Range("B65536").End(xlUp))
'the range to work with
For Each rngCell In rngMyRange
'loop through the range
If rngCell.Value <> rngCell.Offset(1, 0).Value Then
.HPageBreaks.Add Before:=rngCell.Offset(1, 0)
End If
Next
End With

End Sub</pre>HTH

oopsi I made a brain fart in my first code
Try this. i basically was telling it to go check the previous record for a change at cell b1. so it obviously could not find any data from before cell B1.

I did it out of my head and did not actually try it.

sorry for the oopsie

Sub pagebrk()
col = 2 ' what ever column you want to measure.
LastRw = ActiveSheet.UsedRange.Rows.Count
For x = 2 To LastRw 'I had it checking to close to the beginning it could not go 1 row back from row one.
If Cells(x, col) <> Cells(x - 1, col) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(x, col)
End If
Next
End Sub

Below is the working spreadsheet with macro.

just run the pgbreak macro.

Richie(UK)

If you have not already checked mine out please do so.

I used to use methods like yours but I have found using the Cells and lastrow stuff to be more efficient. Instead of moving about the sheet and wasting time you can use variables. The other thing about these variables is the flexability.

I would highly recommend that you experiment with Cells rather than offset ranges.

I also was able to perform mine in 7 lines to the 14 lines of yours.

I found the conversion pretty easy myself once I stumbled upon it.

please comment and tell me what you think.

Edit:WillR[Yes]

Re: VBA: Adding Conditional Page Breaks

I have tried 2 other attempts including this. Everyone of them works good, but I keep having the same problem. I have row A repeat at the top of every sheet. When I run this, my first sheet is always a blank sheet with jsut the header row. It breaks on row a. Grrr. Can anyone help?

Other than that it works great!

Dim X As Long
Dim Rg As Range
On Error Resume Next
Set Rg = Selection 'Range("C4:C15")
For X = Rg.Cells.Count To 1 Step -1
If Rg.Cells(X) <> Rg.Cells(X).Offset(-1) Then
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rg.Cells

(X).EntireRow
End If
Next X
_____________________-
original page break -

For i = 2 To ActiveSheet.UsedRange.Rows.Count
If Cells(i, 2).Value <> Cells((i - 1), 2).Value Then
Cells(i, 1).PageBreak = xlPageBreakManual
End If
Next

--------

Re: VBA: Adding Conditional Page Breaks

p.s. those 2 at the bottom are the other 2 that I have tried, which both give me the same problem.

Re: VBA: Adding Conditional Page Breaks

I know this post was long abandoned, but in case someone else searches and comes across it like I did, I wanted to add my spin. In my research and what I have found was that both the automatic and manual page breaks exsisted together. In the code I wanted I did not want to use the automatic page breaks and have found many others also looking for this without an answer. I used Richie(UK) code as a starting point and with a little help from recording I came up with this. In my code I wanted to add a page break above any cells where they were merged in column B.

Sub Add_PB()
Dim rngMyRange As Range, rngCell As Range
Dim HPRow As Integer, i As Integer

Application.ScreenUpdating = False

Set rngMyRange = Range(Range("B2"), Range("B65536").End(xlUp))

With ActiveSheet

HPRow = .HPageBreaks.Count

For Each rngCell In rngMyRange

If rngCell.MergeCells Then
i = i + 1
If i > HPRow Then
.HPageBreaks.Add Before:=rngCell
Else
Set .HPageBreaks(i).Location = rngCell
End If

End If

Next rngCell
End With
End Sub