how to create a dynamic print area

hi Folks
How can I create a print area in Excel 2010 to be dynamic so that when new data is added, the print area expands automatically? Thank you
Who is Participating?
Rob HensonConnect With a Mentor Finance AnalystCommented:
Do you want the Print Area to be all rows and all columns of the Active Sheet.

If so, just delete the Print Area settings altogether and let the Automatic Print Range to kick in.

Rob H
SreeramConnect With a Mentor Commented:

Using Macro also can be done

sample code:

Sub Macro1()

    With ActiveSheet.PageSetup
        .PrintArea = "$A$1:$D$" & lastinsertedrow
    End With
End Sub


lastinsertedrow - insert the last used row number

this code will set the print area to the range A1 to Dlastinsertedrow
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Rgonzo1971Connect With a Mentor Commented:

With a macro, I would rather do it like this so you have it all automatic

Sub Macro1()
    With ActiveSheet
        .PageSetup.PrintArea = .UsedRange.Address
    End With
End Sub

Open in new window

yuppyduConnect With a Mentor Commented:
Why don't you simply create a variable range name and point to it?

If what you need to print is in range A1 to C40 on Sheet1 and you add items either vertically or horizontally you can name the range as PrintRange and input the following string in the Refers to:

Then you print PrintRange
agwalshAuthor Commented:
What I did in the end was a version of what was here. I set up a dummy print area and then amended the Print Range range name to read ='Name of Sheet'!$A:$A,'Name of Sheet'!$1:$2
And that actually worked well. Checked it with adding and deleting rows and it worked fine. But thank you all :-)
agwalshAuthor Commented:
I've given you all equal marks to say thank you...but I've marked RobHenson's one as best because it was the closest to what worked for me. But THANK YOU to all of you :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.