Dynamic print range problem

Chris Davison

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,790
Evening all,

I've set up a dynamic named range which expands and contracts as rows and columns are added or deleted and called the range "test"

if I use "test" in the print range box of page setup it retains the original size of the range when it was entered....

ie when the range contracts, it still prints the blank rows, keeping the original reference A1:G400 rather than "=test"

am I missing something ? I want the print range to expand and contract accordingly.

many thanks
Chris
:)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
On 2002-04-07 14:58, Chris Davison wrote:
Evening all,

I've set up a dynamic named range which expands and contracts as rows and columns are added or deleted and called the range "test"

if I use "test" in the print range box of page setup it retains the original size of the range when it was entered....

ie when the range contracts, it still prints the blank rows, keeping the original reference A1:G400 rather than "=test"

am I missing something ? I want the print range to expand and contract accordingly.

many thanks
Chris
/board/images/smiles/icon_smile.gif

Chris,

Can you indicate the current print range: Is it A1:G400? If so, File|Print Are|Set Print Area should simply work.

Aladin
 
Upvote 0
thanks Aladin.... but no, that's what I thought also : I entered the print range as "=test" and later as "test" but it replaces this name with the actual range ie A1:G400 which doesn't change when the named range contracts...

weird
 
Upvote 0
On 2002-04-07 14:58, Chris Davison wrote:
Evening all,

I've set up a dynamic named range which expands and contracts as rows and columns are added or deleted and called the range "test"

if I use "test" in the print range box of page setup it retains the original size of the range when it was entered....

ie when the range contracts, it still prints the blank rows, keeping the original reference A1:G400 rather than "=test"

am I missing something ? I want the print range to expand and contract accordingly.

many thanks
Chris
/board/images/smiles/icon_smile.gif

Define the name, Print_Area, using the same formula that "test" refers to.
 
Upvote 0
On 2002-04-07 15:37, Chris Davison wrote:
guess what ?

it works !

thanks Mark, thanks Aladin

/board/images/smiles/icon_smile.gif

Not so fast.

If you activate File|Print Area|Set Print area and you select the area of interest by selecting column indicators A thru G, you'll have the same result as when you define Print_Area to refer to an OFFSET formula.

I remember to have proposed at the old board using an OFFSET formula as reference to the reserved name "Print_Area" and being corrected by a one-time visitor how unnecessary that move was. Whence my question.

Aladin
 
Upvote 0
thanks Aladin,

if I add further info in columns H,I,J,K (row1), the print area doesn't update if I use your non-offset method, it's not dynamic; I'd have to update it manually.

(using the page break preview mode, you can actually see the ranges increase and decrease with the Print_Area = offset etc method)

are you saying someone advised you it was not necessary to use the offsetted print_area ? I'd say they were wrong, the file / print area / set print area is static, not dynamic...




edit - it updates if you *insert* into the range, but not if you augment by just including (ie typing or pasting) extra data into further columns
This message was edited by Chris Davison on 2002-04-07 16:15
 
Upvote 0
On 2002-04-07 16:08, Chris Davison wrote:
thanks Aladin,

if I add further info in columns H,I,J,K (row1), the print area doesn't update if I use your non-offset method, it's not dynamic; I'd have to update it manually.

(using the page break preview mode, you can actually see the ranges increase and decrease with the Print_Area = offset etc method)

are you saying someone advised you it was not necessary to use the offsetted print_area ? I'd say they were wrong, the file / print area / set print area is static, not dynamic...




edit - it updates if you *insert* into the range, but not if you augment by just including (ie typing or pasting) extra data into further columns
This message was edited by Chris Davison on 2002-04-07 16:15

Rowwise it's dynamic, really. You have to select the range by using column selector. If your range is changing columnwise, select the range using row selector. That works too!

Aladin
This message was edited by Aladin Akyurek on 2002-04-07 16:37
 
Upvote 0
I'm not convinced the two methods are the same :)

I can only select by rows or by columns, but not both...... if I select by column to include all rows, my columns are static; if I select by rows to include all columns, my rows are static !

if I click on the top left "all", that's basically the deafult everything, not the same as, say, a print range defined by a dynamic named range (Print_Area)
 
Upvote 0
On 2002-04-07 16:58, Chris Davison wrote:
I'm not convinced the two methods are the same /board/images/smiles/icon_smile.gif

I can only select by rows or by columns, but not both...... if I select by column to include all rows, my columns are static; if I select by rows to include all columns, my rows are static !

if I click on the top left "all", that's basically the deafult everything, not the same as, say, a print range defined by a dynamic named range (Print_Area)

Hee, I'm not saying that both methods are eq. I didn't see your dyn range formula, so I just wanted to signal the easy way in case your situation might fit it.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
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