Make page breaks land only on rows with bottom border

[bmwilliams42]

Ihave a long report consisting of multi-line chunks of data separated byborders, and I have to print a new updated report every few weeks. Forreadability reasons, when it prints, the chunks of data cannot be split betweentwo pages, so if Excel puts a page break in the middle of a chunk, it has to bemoved. If it lands on a border, things are fine. The largest a chunk gets is about 10 rows, so we never have anything large enough to actually require splitting.

Presently,I have to go through and set all the page breaks manually every time wereprint. I've been trying to figure out how to automate this process, but havenot been successful.

I just need to figure out a script that will check if Excel's automatically generated pagebreak lands on a row that has a bottom border, and if not, move it up to onethat does.

All of my searches are turning up code to put a page break on every border, which is not what I need in this context, and I am not skilled enough with VBA yet to try to adapt it to my needs.

Any help would be greatly appreciated! Thank you.

 

[John_w]

See if this does what you want. Run the macro on a copy of your workbook.

Option Explicit

Public Sub Move_Page_Breaks()

    Dim ws As Worksheet
    Dim saveActiveCell As Range
    Dim lastRow As Long, r As Long
    Dim pb As HPageBreak
    
    'Look on the active sheet in the active workbook
    
    Set ws = ActiveWorkbook.ActiveSheet
    Set saveActiveCell = ActiveCell
    
    With ws
          
        lastRow = .UsedRange.Rows.Count - .UsedRange.Row + 1

        'Select last cell so that Excel recalculates automatic page breaks. Can prevent "Subscript out of range" error when looping through HPageBreaks
        
        .Cells(lastRow, "A").Select
        
        For Each pb In .HPageBreaks
            Debug.Print pb.Location.Address
            
            'Does this page break land on a row (the row above) with a bottom border?
            
            If .Cells(pb.Location.Row - 1, "A").Borders(xlEdgeBottom).LineStyle <> xlContinuous Then
            
                'No, so look for nearest row above with a bottom border and if found move this page break to that row

                r = pb.Location.Row
                Do
                    r = r - 1
                Loop Until r = 1 Or .Cells(r, "A").Borders(xlEdgeBottom).LineStyle = xlContinuous
                
                If .Cells(r, "A").Borders(xlEdgeBottom).LineStyle = xlContinuous Then
                    If pb.Type = xlPageBreakAutomatic Then pb.Type = xlPageBreakManual 'must change automatic page break to manual in order to delete it
                    pb.Delete
                    .HPageBreaks.Add Before:=.Cells(r + 1, "A")
                Else
                    MsgBox "Row with bottom border not found above horizontal page break on row " & pb.Location.Row - 1 & _
                        " - therefore page break not moved."
                End If
                
            End If

        Next
    
    End With
    
    'Restore active cell
    
    saveActiveCell.Select
    
End Sub

 

[bmwilliams42]

I apologize for the delay in responding - got swamped with other projects at work.

Thanks for your help! The macro works... On about half the document. There's still auto-generated page breaks landing in the middle of data clusters, and strange divisions where it opted to put a page break, then a row of data, then another page break immediately.

I uploaded a dummy version of the report with the macro run on it so you can see what I mean https://drive.google.com/file/d/0B5bbABBMnsvrN0xnQzFjYnc2alk/view?usp=sharing

One of the "bad" breaks is the end of page 2, and the first of the tiny pages is page 13.

It saves work, though, having half of them done instead of having to do them all every time!

 

[John_w]

Just had another look at this and realised the algorithm is wrong and may result in leaving some automatic page breaks in the wrong place, as you report.

The algorithm loops through existing page breaks (i.e. the automatic page breaks) moving them to the previous row with a bottom border, whereas it should clear the existing page breaks and loop through every row looking for a row with a bottom border. Then if the number of rows in that 'chunk' doesn't fit in the current page (Excel defaults to 50 rows per page) then insert a page break after the previous row which has a bottom border. Does that make sense? Let me know if you want the macro improved this way and I'll have a look at rewriting it.