Add blank rows to fill a page

[rrenis]

Hi, I'm using the following code to save me removing and re-adding lines to the bottom of each page in a spreadsheet (the sheet in question is framed with a border for each page) when I insert or delete row. Whilst it saves time re-formatting when a hard copy is needed the only thing missing is the final aesthetic of 'padding out' the final page of the spreadsheet with blank rows to fill the page so that it is framed as a full page when printed out.

For Each rw In range("A6:XFD10000").Rows
rw.Borders(xlEdgeBottom).LineStyle = xlNone
Next

For Each pgbr In ActiveSheet.HPageBreaks
With pgbr.Location.Offset(-1, 0).EntireRow.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Next

Whilst it's no real hardship inserting or deleting a few rows to ensure the final page of the spreadsheet is a full page with a border when printed but I just wondered whether anyone out there has found themselves in this position and found a solution that they would be willing to share so that it could all be done from a single macro? :*)

Thanks in advance :)

Cheers,
rrenis

[westconn1]

return the printarea, then resize it to match the number of rows required for the number of pages after the last pagebreak

try like
Dim s As Worksheet
Set s = ActiveSheet
linesperpage = s.HPageBreaks(s.HPageBreaks.Count -1).Location-s.HPageBreaks(s.HPageBreaks.Count -2).Location
v = s.HPageBreaks.Count
'c = s.PageSetup.c
Set addy = s.Range(s.Cells(1, 1), s.Cells((v + 1) * linesperpage, s.Cells(1, s.Columns.Count).End(xlToLeft).Column))
s.PageSetup.PrintArea = addy.Address

[rrenis]

Hi westconn1, thanks for taking the time to reply and post your code. Not sure what I'm doing wrong but I've just copied the code into a sub to test it and it returned 'runtime error 9' 'subscript out of range' on the third line (linesperpage =...). Any idea what I'm doing wrong? Thanks in for your help!!

Cheers,
rrenis

[westconn1]

must be less than 3 hpagebreaks

you can read the number of lines in the first page break, but if (as my test sheet) page 1 contains header rows an incorrect value may be returned
i initially set linesperpage doing a manual count, but decided it should be automated
check if page 2 is more than page 1 then use that value, if there is less than 2 full pages, it would not be possible to do a proper linecount
if page 1 does not contain header rows you can just use the line count of page 1