Setting Print Area in a Macro - PLEASE HELP!

smugtim

Board Regular
Joined
Mar 7, 2005
Messages
54
Please could somebody help me.

I need to set the print area in a macro. The range that I want to set the print area to is returned to a cell.

For example:

Cell B5 contains the value: $B$1:$O$50.
In the macro I have the line: ActiveSheet.PageSetup.PrintArea = ???

What do I put in place of the ??? above, to retrieve the value from B5?

Thank you!

TIM[/b]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Does this work?
Code:
ActiveSheet.PageSetup.PrintArea = Range("B5").Value
 
Upvote 0
Since the addresses are in the cell, you should be able to use:

ActiveSheet.PageSetup.PrintArea = Range("B5").Value
 
Upvote 0
Thank you so much for your quick reply. I knew there should be a very simple answer!

I couldn't believe that VB wouldn't allow a reference to a cell containing a range rather than the range itself. But couldn't find any information on this in Help.

Cheers!

TIM
 
Upvote 0
Yeah. For the Print Range, you can't actually refer to it as a range--you have to enter the addresses. So if you did not have the range written in a cell, you would have to use

ActiveSheet.PageSetup.PrintArea = Range("B1:O50").Address

so that it would return $B$1:$O$50 as a string.

It's goofy :)
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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
Back
Top