Solved

Print area in Excel, how to assign it to a range

Posted on 2011-03-02
6
322 Views
Last Modified: 2012-05-11
I am tring to devleop a resizing program for our users to use during our massive rebranding project.  A report could have three columns, or five, but it has to remain with certain margin paramaters.  So, the first step I thought would be to turn the PageArea that is set by the user into a range (due to spaces between rows for "prettiness" cannot simpy use the UsedRange function).  Is that possible?  To take the PrintArea and assign it a RangeName with VBA?
0
Comment
Question by:ssmith94015
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35018978
How about this VBA code?

ActiveSheet.PageSetup.PrintArea = Range("SomeRange").Address

Where "SomeRange" is the named range.
0
 

Author Comment

by:ssmith94015
ID: 35019008
I need to turn the PrintArea set by the user into a named range.  That is, create the named range from the PrintArea.
0
 
LVL 9

Expert Comment

by:McOz
ID: 35019052
You can assign printarea to a named range without VBA. See screencast:
McOz-415518.flv
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 9

Expert Comment

by:McOz
ID: 35019065
Oops! missed your comment above. To set the RangeName to the Print_Area, just do the same in reverse.

Good luck!
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 35019106
It actually is already a named range called Print_Area.  you'll see if if you go to the name box (top left corner, just above the grid, click the dropdown arrow)
To you want it to have a different name also?


    ActiveWorkbook.Names.Add "NewName1", _
        "=" & Range("Print_Area").Address
0
 

Author Closing Comment

by:ssmith94015
ID: 35019197
rspahtiz, that was what I was trying to find!  I figured it was somewhere but I was just not putting in the correct search criteria.

McOz, liked the video, but this all has to be done in VBA.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question