Excel 2013 VBA: remaining row count from lastrow to end of page

[jedwardo]

Hi, I'm trying to find a way with VBA to get the number of empty rows left on a page on Sheet2. I then want to compare that number to another in "Sheet1 A1". If the 2nd number is greater than the row count I want to insert a pagebreak on Sheet2. Is there a way to do this? I can't find much of anything about end of page on a sheet.

 

[jedwardo]

Thanks Teeroy, I'll see what I can do with that. Thought I had it knocked out yesterday for a sec. I added this to the loop:

Dim PBJ As Integer

[COLOR=#0000ff]' and then this in red at the end[/COLOR]

           [COLOR=#ff0000] PBJ = ws1.Range("cg14")
            Break = Sheets(6).HPageBreaks(PBJ).Location.Row         [/COLOR]            
            RowBot = LastUsedRow                                                            
            c = "a"                                                               
            ws1.Cells(iCell.Row, 86).Value = RowTop                               
            ws1.Cells(iCell.Row, 87).Value = RowBot                                
           [COLOR=#ff0000] ws1.Cells(iCell.Row, 88).Value = Sheets(6).HPageBreaks(PBJ).Location.Row
                If Break > RowTop And Break <= RowBot Then
                    ws.Rows(RowTop).PageBreak = xlPageBreakManual
                    ws1.Range("cg14") = ws1.Range("cg14") + 1
                    PBJ = ws1.Range("cg14")[/COLOR]

PBJ is a counter cell that starts at 1 and is increased by 1 every time a page break is added. It works for the break in between the first and second pages and keeps any room blocks from being split across 2 pages there but doesn't work for any pages beyond that. After the first manual break is added by the code doesn't pick up on the next natural break to keep the next room block from splitting.

I was thinking maybe I need to redefine the print area after the next loop but before the reference to the hpagebreak is made.

 

[jedwardo]

Got this working.. I scrapped the pagebreak route and just set my integer to 47 (always first pagebreak). Then added 44 (rows on each consecutive page) to my integer after each manual page break gets placed. Break is integer.

If Break > RowTop And Break <= RowBot Then               
ws.Rows(RowTop).PageBreak = xlPageBreakManual
Break = RowTop + 44

That's nearly 3 weeks of code right there hahaha

 

[Teeroy]

Another way to do that without keeping track of all the variables is:

Sub fixed_pagebreaks()
With ActiveSheet.UsedRange
    For i = 47 To .Rows(.Rows.Count).Row Step 44
        Rows(i).PageBreak = xlPageBreakManual
    Next i
End With
End Sub

 

[jedwardo]

Thanks, that'll clean up my loop a bit. It's gets larger every day

 

[Teeroy]

Thanks, that'll clean up my loop a bit. It's gets larger every day

All code seems to do that .