Filling to end of page with borders / print area

[mharper90]

I am using code to apply basic borders around all used cells in a worksheet. Perfectly, this applies borders to the anticipated range. I use the same range to set the print area. I'd like to modify the macro to modify this range so as to set the print area AND apply borders to all cells required to fill the page. In other words, if there are 15 rows, but a page could be 40, I want the print area set to row 40, and borders applied to the same range, so that when printed, it appears as a full page (with 25 blank rows of just borders). Then, if there are 50 rows, the macro should set the print area and borders to 80 rows, to completely fill 2 pages (with 30 blank rows of just borders).

ws.range("A1:E" & wsLr).borderaround

 

[KennyGreens]

Hi there, will each page always be 40 rows?

 

[mharper90]

Hi there, will each page always be 40 rows?

I'm not sure of the exact number, and the file is at work so I can't check right now. But the rows are consistent height, so whatever variable it is that equals a total page could easily be changed, I imagine. Judging from other projects I have, 40 is probably a good starting point.

 

[Fluff]

How about

Sub mharper90()
   Dim Lr As Long, Trws As Long
   
   Lr = Range("A" & Rows.count).End(xlUp).Row
   Trws = Application.RoundUp(Lr / 40, 0) * 40
   With Range("A1:E" & Trws)
      .Borders.Weight = xlThin
      .Parent.PageSetup.PrintArea = .Address
   End With
End Sub