Add extra rows to print region

VBAProIWish

Well-known Member
Joined
Jul 6, 2009
Messages
980
Hello All,

I have this code here that sets the print area based on the current region...

Code:
 ActiveSheet.PageSetup.PrintArea = [A1].CurrentRegion.Address

How can I modify that code to add, let's say, 5 extra rows to the print area? (keeping the column count the same)

I tried several things. Here's a few I tried that didn't work...

Code:
  ActiveSheet.PageSetup.PrintArea = [A1].CurrentRegion.Address.Offset(5)
  ActiveSheet.PageSetup.PrintArea = [A1].CurrentRegion.Offset(5)
  ActiveSheet.PageSetup.PrintArea = [A1].CurrentRegion.xlDown.Offset(5)

What am I doing wrong?

Thanks
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
The "Print_Area" is just a named range with the special reserved name "Print_Area" (you can see it in the "Names" box). Try going after it as though it were any other named range. Maybe something like the sample below.

Hope it helps.

Gary

Code:
Public Sub Test()

Dim oRange As Range

Set oRange = Range("Print_Area").Resize(Range("Print_Area").Rows.Count + 5)

ActiveSheet.PageSetup.PrintArea = oRange.Address

End Sub
 

Forum statistics

Threads
1,141,018
Messages
5,703,754
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top