Print Area:when it comes to printing,

Hi

I have data constantly changing from using 100 to 1500 lines and when it
comes to printing, I have to constantly set the print area or drag the print
preview handles. Is there a way that a piece of code can find the last
occupied cell in a column and have the page set up change accordingly?

Many thanks in advance

John

John,
With ActiveSheet
.PageSetup.PrintArea = .UsedRange.Address
End With

NickHK

"JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
news:64553F53-5570-42A6-A503-39855B6B95DA@microsoft.com...
> Hi
>
> I have data constantly changing from using 100 to 1500 lines and when it
> comes to printing, I have to constantly set the print area or drag the
print
> preview handles. Is there a way that a piece of code can find the last
> occupied cell in a column and have the page set up change accordingly?
>
> Many thanks in advance
>
> John

Hi Nick, thanks for your help.
I have tried it, but it doesnt work, maybe because I have formulas that run
down each side of the data that I want printed!!
John

"NickHK" wrote:

> John,
> With ActiveSheet
> .PageSetup.PrintArea = .UsedRange.Address
> End With
>
> NickHK
>
> "JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
> news:64553F53-5570-42A6-A503-39855B6B95DA@microsoft.com...
> > Hi
> >
> > I have data constantly changing from using 100 to 1500 lines and when it
> > comes to printing, I have to constantly set the print area or drag the
> print
> > preview handles. Is there a way that a piece of code can find the last
> > occupied cell in a column and have the page set up change accordingly?
> >
> > Many thanks in advance
> >
> > John
>
>
>

Hi John,

With ActiveSheet
.PageSetup.PrintArea =
intersect(.UsedRange.Address,range("a:b")).address
End With

Change "a:b" to be columns you want printed.

Regards,
Ivan

Hi Ivan - again

It comes back as a run time error - object required

Any ideas ?

John

"Ivan Raiminius" wrote:

> Hi John,
>
> With ActiveSheet
> .PageSetup.PrintArea =
> intersect(.UsedRange.Address,range("a:b")).address
> End With
>
> Change "a:b" to be columns you want printed.
>
> Regards,
> Ivan
>
>

Hi John,

sorry, should be:
intersect(.UsedRange,range("a:b")).address

Regards,
Ivan

John,
I'm sure Ivan will see it, but remove the .address from .usedrange.

NickHK

"JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
news:5740B382-FDAF-4C77-A02E-2E53BD7839CB@microsoft.com...
> Hi Ivan - again
>
> It comes back as a run time error - object required
>
> Any ideas ?
>
> John
>
> "Ivan Raiminius" wrote:
>
> > Hi John,
> >
> > With ActiveSheet
> > .PageSetup.PrintArea =
> > intersect(.UsedRange.Address,range("a:b")).address
> > End With
> >
> > Change "a:b" to be columns you want printed.
> >
> > Regards,
> > Ivan
> >
> >