Macro/VBA to Set Print Area plus Blank Rows

[wirra]

Hi,

I need a macro to set the print area for the data in my worksheet plus 10 extra blank rows. In the example below I need to set the print area from A2 to E21. The amount of rows of actual data will change regularly but the columns will stay the same ie. A to E. So in essence I need to set the print area for whatever amount of data in columns A to E plus 10 blank rows at the end.

Hope that makes sense.

Thanks
Bob

Excel Workbook
	A	B	C	D	E
1		Last Name	First Name	Date Paid
2		Aeton	Ron	30-Jun-17
3		Albert	Vicki	04-May-17
4		Algester	Lesley	12-Jul-17
5		Bloggs	Bob	27-Jun-17
6		Brown	Glen	05-Jul-17
7		Cruise	Amanda	05-Jul-17
8		Devlon	Robert	07-Feb-18
9		Faston	Mick	27-Jun-17
10		Gothic	Bob	05-Apr-17
11		Harriet	Beverly	02-Aug-17
12
13
14
15
16
17
18
19
20
21
Sheet1

 

[footoo]

With Range([A1], ActiveSheet.UsedRange)
ActiveSheet.PageSetup.PrintArea = .Resize(.Rows.Count + 10).Address
End With

 

[wirra]

Hi footoo,

Thanks very much for your reply. Excuse my ignorance but it seems to me that if I add borders to the blank rows below my data as shown in my example above then your macro sets the print area to 10 rows below where my formatting finishes. Is that correct? Also I don't want to include row 1 in the print area as I want this row of titles to repeat on each page. In essence my worksheet is set up with borders, fonts and row heights for many rows below my data. What I want to do is to set the print area for A2:E? for the data plus 10 rows below. Is this possible?

Not sure if this makes sense but hopefully you can help.

Regards,

Bob

 

[footoo]

Assuming column B can be used to find the last data row :

ActiveSheet.PageSetup.PrintArea = Range([A2], Cells(Cells(Rows.Count, "B").End(xlUp).Row + 10, "E")).Address

 

[wirra]

That's works. Thanks very much for your help footoo.

Cheers,

Bob