Can anyone give me a routine for automatically printing a range based on how
many rows of a spread sheet are filled? I think I could set up a dynamic
range to determine all the rows filled with data but then I need a VBA
routine to actaully select that range and set it as the print area and then
print or go into print preview mode. Any help would be much appreciated.
Thanks,
RD Wirr

clear the print area..and you'll get the same result.

Caveat: ensure your "UsedRange" is not contaminated
by formatting outside of your data area. check that
by pressing ctrl-end.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


RD Wirr wrote :

Can anyone give me a routine for automatically printing a range based
on how many rows of a spread sheet are filled? I think I could set up
a dynamic range to determine all the rows filled with data but then I
need a VBA routine to actaully select that range and set it as the
print area and then print or go into print preview mode. Any help
would be much appreciated. Thanks,
RD Wirr

Hi RD,
Wirr wrote:
I think I could set up
a dynamic range to determine all the rows filled with data

yes, that works.

but then I
need a VBA routine to actaully select that range and set it as the
print area and then print or go into print preview mode.

no! you do not need vba for this. use the offset()-function to define
the name for the range "printarea" (I do not know the exact name in
english excel, you can find it in the list of names).

Any help
would be much appreciated.

If you fill your sheet only with data that should be printed and you
want to print all rows, then manually set the printarea to the
_whole_columns_ that you want to print. printing will stop at the last
filled row then! (This will _not_ work if you have eg. 100 rows with
formulas, but only 50 records, but then the dynamic range would work.)

arno

PS:
my solutions are supposed to be used for sheets that contain data that
should not be printed. eg. you have some headlines in rows 1 to 5 and
some columns starting with H that must be in the sheet but should not
be printed. so, you need to print from A6 to G???? depending on how
many records you have.

define the printarea with the following formula, if the number of
records plus report-headlines is in range "recnr":
=offset($A$6, 0, 0, recnr, 7)

arno

it appears i misread the question.

since Print_Area is a builtin name
dont use it to define a "dynamic name" formula
as it will be overwritten all to easily.

what you can do is define a "dynamic name"
like MyPrintRange
then use vba to set Printarea = Range("myprintrange").address

Of you could do it entirely in vba.
there are many techniques to get the lastrow in a range..
this is one..

Activesheet.printarea = _
Range("a1",Range("a1").end(xldown)).address




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :


clear the print area..and you'll get the same result.

Caveat: ensure your "UsedRange" is not contaminated
by formatting outside of your data area. check that
by pressing ctrl-end.


--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam

Wow, you guys are fast. Thanks I've tried em both and they both do what I
need.
Thanks again,
RD

"arno" wrote:

Hi RD,
Wirr wrote:
I think I could set up
a dynamic range to determine all the rows filled with data

yes, that works.

but then I
need a VBA routine to actaully select that range and set it as the
print area and then print or go into print preview mode.

no! you do not need vba for this. use the offset()-function to define
the name for the range "printarea" (I do not know the exact name in
english excel, you can find it in the list of names).

Any help
would be much appreciated.

If you fill your sheet only with data that should be printed and you
want to print all rows, then manually set the printarea to the
_whole_columns_ that you want to print. printing will stop at the last
filled row then! (This will _not_ work if you have eg. 100 rows with
formulas, but only 50 records, but then the dynamic range would work.)

arno

hi,

dont use it to define a "dynamic name" formula
as it will be overwritten all to easily.

good point, however, it never happened to me. original reports are
always writeprotected, one could change but not save.

arno

how can i do the same thing with VFP 6.0 i have been trying stuff but
nothing seems to work.