Page Breaks that don't split data blocks (VBA)

[Rulenine]

Hi all,

I am currently working on a project that generates automatic quotes for customers. I have set it up so that I have various sections (construction, electrical, equipment, hardware, etc) and the quote automatically pulls in whatever I used in my estimator as the scope of work and then generates a total at the bottom of each section. If a section's total is 0, it hides the entire section so only relevant data is displayed.

My problem is that I am getting page breaks in really bad spots if certain sections get hidden. For example, if I don't need hardware and all those rows get hidden, my equipment section lists all the scope of work (one item per row) and then the total gets cut off and is on the next page. I would like to find a way to use VBA (which I've used for all the other stuff already) to place the page break in a location that:
a) is close to a full page (so I don't want a page break at the end of each data section)
b) keeps all data in a certain section together.

Any ideas? Even some ideas of how to block off the data but still make sure I am not inserting page breaks too soon would be enough to maybe trigger a solution in my own mind

Thanks!

 

[dchaney]

This is one that I use on a project of mine

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
        
    ws.Activate

    With ws
    '****************************************************************************************
    'MOVE VERTICAL PAGE BREAK AFTER COLUMN G                                                *
    '****************************************************************************************
        ActiveWindow.View = xlPageBreakPreview
        ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
        
    '****************************************************************************************
    'IDENTIFY AND MOVE EACH HORIZONTAL PAGE BREAK TO PREVENT SEPERATING UNIT BLOCKS         *
    '****************************************************************************************
        i = 1
        x = ws.HPageBreaks.Count
        
            Do Until i > x
                y = ws.HPageBreaks(i).Location.Row
                i = i + 1
                x = ws.HPageBreaks.Count
            Loop
        
        ActiveWindow.View = xlNormalView
    
    End With

Next