setting print area

Hi,

Can anyone help me with a macro to search down column A from row 15 onwards
(inclusive) until it finds the last cell containing any text (cell #), and
then set the print area to cells A1:X#, where # is the last cell in column A
containing any text?

Any help with this would be much appreciated!

Thanks in advance,

Nick

Hi Nick,

Try:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
Dim rng As Range
Dim LRow As Long

Set SH = ActiveSheet '<<==== CHANGE

With SH
LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
Set rng = .Range("A15:A" & LRow)
.PageSetup.PrintArea = rng.Address
End With

End Sub
'<<=============


---
Regards,
Norman


"Nick Smith" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Can anyone help me with a macro to search down column A from row 15
> onwards
> (inclusive) until it finds the last cell containing any text (cell #), and
> then set the print area to cells A1:X#, where # is the last cell in column
> A
> containing any text?
>
> Any help with this would be much appreciated!
>
> Thanks in advance,
>
> Nick

Hi Nick,

Change:

> Set rng = .Range("A15:A" & LRow)

to

Set rng = .Range("A15:X" & LRow)

where X is the last column to be printed.


---
Regards,
Norman

Another thank you. Works a treat.

"Norman Jones" wrote:

> Hi Nick,
>
> Try:
>
> '=============>>
> Public Sub Tester()
> Dim SH As Worksheet
> Dim rng As Range
> Dim LRow As Long
>
> Set SH = ActiveSheet '<<==== CHANGE
>
> With SH
> LRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
> Set rng = .Range("A15:A" & LRow)
> .PageSetup.PrintArea = rng.Address
> End With
>
> End Sub
> '<<=============
>
>
> ---
> Regards,
> Norman
>
>
> "Nick Smith" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > Can anyone help me with a macro to search down column A from row 15
> > onwards
> > (inclusive) until it finds the last cell containing any text (cell #), and
> > then set the print area to cells A1:X#, where # is the last cell in column
> > A
> > containing any text?
> >
> > Any help with this would be much appreciated!
> >
> > Thanks in advance,
> >
> > Nick
>
>
>