VBA move print area down 1 row from active cell

[willow1985]

Hello,

I was just wondering what would be the code to adjust a print area down 1 row without using specific ranges?

I currently have a macro that finds the end of a table and adds a row for new data however I would like to expand the print area after I add the row by 1 row.

the only code I know is: ActiveSheet.PageSetup.PrintArea = "your range"

Any help would be appreciated.


Thank you

Carla

 

[JoeMo]

Not sure I understand exactly what you want. This offsets the print area by 1 row and adds 1 more row to it. Adjust to suit ...

Sub try()
With ActiveSheet
   .PageSetup.PrintArea = _
       .Range("Print_Area").Offset(1).Resize(.Range("Print_Area").Rows.Count + 1, _
       .Range("Print_Area").Columns.Count).Address
End With
End Sub

 

[willow1985]

My mistake, the selected cell actually ends up just below the page break line so this code actually moves it one row lower than required. How could I adjust this code that it is even with the selected cell?

 

[willow1985]

Nevermind, figured it out:

    With ActiveSheet   .PageSetup.PrintArea = _
       .Range("Print_Area").Offset(0).Resize(.Range("Print_Area").Rows.Count + 1, _
       .Range("Print_Area").Columns.Count).Address
End With

Thank you very much!!!

 

[MARK858]

If you are Offsetting by 0 then you might as well just remove it

 

[JoeMo]

Nevermind, figured it out:

    With ActiveSheet   .PageSetup.PrintArea = _
       .Range("Print_Area").Offset(0).Resize(.Range("Print_Area").Rows.Count + 1, _
       .Range("Print_Area").Columns.Count).Address
End With

Thank you very much!!!

You are welcome - thanks for the reply.

 

[willow1985]

Actually this does not work. It is still moving down one row more than I need. Not sure how to adjust it that it moves to the selected cell...

 

[willow1985]

Played around a bit and so far this is working:

   .PageSetup.PrintArea = _       .Range("Print_Area").Offset(2).Resize(.Range("Print_Area").Rows.Count - 1, _
       .Range("Print_Area").Columns.Count).Address