Inserting Page Breaks using VBA

[stephensmart]

I have a spreadsheet that prints a completed form that can be a variable number of rows long depending on the responses to questions answered by the user.

I need code that will insert page breaks every 60 rows but not split the tables of data - the tables can vary in size based on the responses to the questions.

There is a blank row between each table.

The maximum number of rows on the spreadsheet is 300.

Nothing I have tried so far has worked

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> PageBreaks60()<br>    <SPAN style="color:#00007F">Dim</SPAN> PBRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Lastrow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#007F00">' reset previous pagebreaks</SPAN><br>    ActiveSheet.ResetAllPageBreaks<br>    <br>    PBRow = 1<br>    Lastrow = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">Do</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> r = PBRow + 59 <SPAN style="color:#00007F">To</SPAN> PBRow <SPAN style="color:#00007F">Step</SPAN> -1<br>            <SPAN style="color:#00007F">If</SPAN> Cells(r, "A").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>                PBRow = r + 1<br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> r<br>        ActiveSheet.HPageBreaks.Add Before:=Rows(PBRow)<br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> PBRow + 59 >= Lastrow<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[stephensmart]

Thanks Alphafrog - this almost works but its still inserting page breaks every 60 rows and splitting my tables.

I have merged cells in column P that match the size of the tables - perhaps these could be used in some way to prevent the page breaks splitting the tables.

 

[stephensmart]

Alphafrog - your code works perfectly thanks - discovered I had blanks in my tables which was causing my problem.

Now I have removed them it does exactly what I need.

You are a life saver as I have spent hours trying to resolve this.

Thank you so much