Printing Using a Dynamic Range Excel 2003

[Tom54]

I have created a dynamic range and it works properly. I can input the dynamic range in the Page Set Up Sheet Print Area and it will print the range.

When I add to the range the dynamic range updates but not the print area in the Page Set Up. It there something I am missing or must a VBA procedure be used.

Regards,

Tom D

[HansV]

Try the following:
- Note the definition of the dynamic range, or copy it to the clipboard. This is usually a formula involving OFFSET and COUNTA.
- Select an arbitrary range on the sheet.
- Select File | Print Area | Set Print Area.
- This will create a named range Print_Area if it didn't already exist.
- Select Insert | Name | Define...
- Select the name Print_Area.
- Replace the contents of the Refers To box with the definition of the dynamic range.
- Click OK.

Use File | Print Preview to check that the print area is updated automatically.

Don't use File | Print Area | Set Print Area again after this, nor change the definition in the Page Setup dialog.

[Tom54]

Hans:

Good news Bad news. The above worked but when I closed the file and then open it the Print_Area is no longer Dynamic. The Workbook is shared and I think this may be causing the issue.

My best non VBA work around was to create a dynamic range with a name like MyPrint1. Once Created I then type that name into the name box. Next I use the Toolbar Button "Set Print Area" prior to printing.

Tom D

[HansV]

Sharing a workbook is a bad idea - it greatly improves the chance of the workbook becoming corrupted.

With a non-shared workbook, the print area remains dynamic after saving, closing and reopening the workbook - at least for me.