Filling to end of page with borders / print area

mharper90

Board Regular
Joined
May 28, 2013
Messages
111
Office Version
  1. 365
Platform
  1. MacOS
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).


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

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

mharper90

Board Regular
Joined
May 28, 2013
Messages
111
Office Version
  1. 365
Platform
  1. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,650
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,314
Members
409,862
Latest member
lbisacca
Top