Set Print Area programatically

Can anyone help me get the syntax right. I have tried variations on the
theme:

Range(Cells(1, 1), Cells(NoOfRows, 29)).Select
ActiveSheet.PageSetup.PrintArea = Selection

but cannot get anything past the debugger!

Thanks
mervyn

Hi Mervyn

Use this
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(NoOfRows, 29)).Address

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mervyn Thomas" <[email protected]> wrote in message news:pZZ%[email protected]...
> Can anyone help me get the syntax right. I have tried variations on the theme:
>
> Range(Cells(1, 1), Cells(NoOfRows, 29)).Select
> ActiveSheet.PageSetup.PrintArea = Selection
>
> but cannot get anything past the debugger!
>
> Thanks
> mervyn
>

Syntax is OK; are you sure NoOfRows gives the correct result? If it is 0,
this would give "Application defined or object defined error."

"Mervyn Thomas" wrote:

> Can anyone help me get the syntax right. I have tried variations on the
> theme:
>
> Range(Cells(1, 1), Cells(NoOfRows, 29)).Select
> ActiveSheet.PageSetup.PrintArea = Selection
>
> but cannot get anything past the debugger!
>
> Thanks
> mervyn
>
>
>

Thanks - that works!
"Ron de Bruin" <[email protected]> wrote in message
news:Ow%[email protected]...
> Hi Mervyn
>
> Use this
> ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(NoOfRows,
> 29)).Address
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Mervyn Thomas" <[email protected]> wrote in message
> news:pZZ%[email protected]...
>> Can anyone help me get the syntax right. I have tried variations on the
>> theme:
>>
>> Range(Cells(1, 1), Cells(NoOfRows, 29)).Select
>> ActiveSheet.PageSetup.PrintArea = Selection
>>
>> but cannot get anything past the debugger!
>>
>> Thanks
>> mervyn
>>
>
>

I thought so too! but the other reply does work
Mervyn
"K Dales" <[email protected]> wrote in message
news:[email protected]...
> Syntax is OK; are you sure NoOfRows gives the correct result? If it is 0,
> this would give "Application defined or object defined error."
>
> "Mervyn Thomas" wrote:
>
>> Can anyone help me get the syntax right. I have tried variations on the
>> theme:
>>
>> Range(Cells(1, 1), Cells(NoOfRows, 29)).Select
>> ActiveSheet.PageSetup.PrintArea = Selection
>>
>> but cannot get anything past the debugger!
>>
>> Thanks
>> mervyn
>>
>>
>>

It miss
..Address after selection

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Mervyn Thomas" <[email protected]> wrote in message news:Ll_%[email protected]...
>I thought so too! but the other reply does work
> Mervyn
> "K Dales" <[email protected]> wrote in message news:[email protected]...
>> Syntax is OK; are you sure NoOfRows gives the correct result? If it is 0,
>> this would give "Application defined or object defined error."
>>
>> "Mervyn Thomas" wrote:
>>
>>> Can anyone help me get the syntax right. I have tried variations on the
>>> theme:
>>>
>>> Range(Cells(1, 1), Cells(NoOfRows, 29)).Select
>>> ActiveSheet.PageSetup.PrintArea = Selection
>>>
>>> but cannot get anything past the debugger!
>>>
>>> Thanks
>>> mervyn
>>>
>>>
>>>
>
>

PrintArea requires a string argument. It resists range objects unless they
contain a string address

maybe selection.Address

--
Regards,
Tom Ogilvy



"K Dales" <[email protected]> wrote in message
news:[email protected]...
> Syntax is OK; are you sure NoOfRows gives the correct result? If it is 0,
> this would give "Application defined or object defined error."
>
> "Mervyn Thomas" wrote:
>
> > Can anyone help me get the syntax right. I have tried variations on the
> > theme:
> >
> > Range(Cells(1, 1), Cells(NoOfRows, 29)).Select
> > ActiveSheet.PageSetup.PrintArea = Selection
> >
> > but cannot get anything past the debugger!
> >
> > Thanks
> > mervyn
> >
> >
> >