Set print area

[tim201110]

Hi,
I set a UDN a1a=l!$A$1:index(l!$A$1:$N$28,l!$J$1,l!$K$1)
I put that name in Set Print Area. It works, but then I changed dimentions (J1 & K1) nothing happened. I looked in Page setup, there was A1:G7 (old area) in Set Print Area inputbox. I understand that i can do it with VBA, but UDN is great feature, why not in this case?
OFFSET works the same way.
I'm not the first to raise it
http://excel-tips.blogspot.ru/2008/1...-in-excel.html.
and here
http://www.excelforum.com/excel-gene...rint-area.html
is there any desision without VBA?

[LJMetzger]

Hi tim201110,

I found what looks like a solution (tested in Excel 2003 only) in using the OFFSET formula and an intermediate named range: http://excel-tips.blogspot.com/2008/...-in-excel.html
See the attached copy of your sample file.

If the intermediate named range is not used, the Print Area seems to reset itself to absolute values.

MyPrintArea:

=OFFSET(l!$A$1,0,0,COUNTA(l!$A:$A),COUNTA(l!$1:$1))



Print_Area:

=MyPrintArea

This has the limitation that there can't be any empty cells in Column 'A' or in Row '1'. If you have other rows/columns with non-blank cells instead of ('A' / '1'), replace the items in red.

Lewis