Print variable page sizes

[nsv]

I have a sheet that varies in number of lines I want to print. Number of columns is constant.

The number of lines is found by adding 20 to the highest number in column A, where a list of items from 1 to x starts in A6. Eg. if x = 17 the area to print is from A1 to N37.

I will need VBA to find out what the value of MAX(A:A) is. How is this done?

The meaning is perhaps easiest understood by following sentence, which unfortunately does not make sense to VBA:
ActiveSheet.PageSetup.PrintArea = "A1:N(MAX(A:A)+20)"


NSV

[Tom Ogilvy]

n1 = Application.Max(Range("A:A")) + 20
ActiveSheet.PageSetup.PrintArea = "$A$1:$N$" & n1


--
Regards,
Tom Ogilvy


"nsv" <[email protected]> wrote in message
news:[email protected]...
>
> I have a sheet that varies in number of lines I want to print. Number of
> columns is constant.
>
> The number of lines is found by adding 20 to the highest number in
> column A, where a list of items from 1 to x starts in A6. Eg. if x = 17
> the area to print is from A1 to N37.
>
> I will need VBA to find out what the value of MAX(A:A) is. How is this
> done?
>
> The meaning is perhaps easiest understood by following sentence, which
> unfortunately does not make sense to VBA:
> ActiveSheet.PageSetup.PrintArea = "A1:N(MAX(A:A)+20)"
>
>
> NSV
>
>
> --
> nsv
> ------------------------------------------------------------------------
> nsv's Profile:
http://www.excelforum.com/member.php...o&userid=26500
> View this thread: http://www.excelforum.com/showthread...hreadid=537469
>

[nsv]

It just works!! Thanks Tom!!

I am only an amateur in VBA, but I have a lot of small tasks to solve when working with Excel and the VBA Help text is not very helpful. It is difficult if not impossible to find this kind of tips systematically, so your help is very appreciated.

Niels