Add extra rows to print region

[VBAProIWish]

Hello All,

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

 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...

  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

 

[Gary McMaster]

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

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

 

[VBAProIWish]

Indeed that did work.


Thanks much !