VBA: Adding Conditional Page Breaks

  • Here's my scenario...

    I need to write a macro that checks a column of numbers, and each time the number changes, insert a page break. For example, if you have a column with all 10's in each row, but on row 25 it then becomes 15's...at that point I would like a page break. Any ideas or assistance?

  • 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


    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.

  • I inputed the code as suggested, and changed the "COL" number to 2, because I want it to evaluate column B. However, when I run this macro, it immediately jumps back to the vb editor with "For x = 1 To LastRw" (without quotes of course) highlighted yellow. No errors are given, and I don't know enough about VB to troubleshoot it.

  • 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
    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
    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.


  • 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

    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


  • 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
    Set .HPageBreaks(i).Location = rngCell
    End If

    End If

    Next rngCell
    End With
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!