ActiveSheet.PageSetup.PrintArea variable ?

Hi,

I have a macro that prints the content od a range of cells and i have it set
to print D,142 to AA,142 using ActiveSheet.PageSetup.PrintArea =
"$D$142:$AA$142"

Every now and then the row changes (people adding/deleting rows) so I have
created a row variable by searching for a string that only appears in the
row I want to print as follows;

CtoPrintRow = "CTO Requests"
r = 1
Do Until CtoPrintRow = (Cells(r, 1))
r = r + 1
Loop

I can't get my head around how I use now use the variable to print that row,
I have fiddled with it as follows but clearly I am missing something, but
what ?

ActiveSheet.PageSetup.PrintArea = "$D$r:$AA$r"

ActiveSheet.PageSetup.PrintArea = "$D$" & r & ":$AA$" & r

--
Regards,
Tom Ogilvy


"MAS" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a macro that prints the content od a range of cells and i have it
set
> to print D,142 to AA,142 using ActiveSheet.PageSetup.PrintArea =
> "$D$142:$AA$142"
>
> Every now and then the row changes (people adding/deleting rows) so I have
> created a row variable by searching for a string that only appears in the
> row I want to print as follows;
>
> CtoPrintRow = "CTO Requests"
> r = 1
> Do Until CtoPrintRow = (Cells(r, 1))
> r = r + 1
> Loop
>
> I can't get my head around how I use now use the variable to print that
row,
> I have fiddled with it as follows but clearly I am missing something, but
> what ?
>
> ActiveSheet.PageSetup.PrintArea = "$D$r:$AA$r"
>
>
>

Thank Tom,



"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> ActiveSheet.PageSetup.PrintArea = "$D$" & r & ":$AA$" & r
>
> --
> Regards,
> Tom Ogilvy
>
>
> "MAS" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> I have a macro that prints the content od a range of cells and i have it
> set
>> to print D,142 to AA,142 using ActiveSheet.PageSetup.PrintArea =
>> "$D$142:$AA$142"
>>
>> Every now and then the row changes (people adding/deleting rows) so I
>> have
>> created a row variable by searching for a string that only appears in the
>> row I want to print as follows;
>>
>> CtoPrintRow = "CTO Requests"
>> r = 1
>> Do Until CtoPrintRow = (Cells(r, 1))
>> r = r + 1
>> Loop
>>
>> I can't get my head around how I use now use the variable to print that
> row,
>> I have fiddled with it as follows but clearly I am missing something, but
>> what ?
>>
>> ActiveSheet.PageSetup.PrintArea = "$D$r:$AA$r"
>>
>>
>>
>
>