Set Print Area

[Porky79]

Hi - I have recorded a simple macro to set a print area on a
worksheet.

Range("A1:C52").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$C$52"

Rows will always remain constant (1:52) but the columns vary (columns
are added from D onwards as data is collected). I want to try and edit
it so that it will set the range automatically. Can I use the OFFSET
function to automatically set this up?

Any help will be much appreciated

thanks

Paul

 

[Don Guillett]

Instead of using a macro why not just modify the Print_Area defined name to
=offset($a$1,0,0,counta($a:$a),3)
where it will now self adjust. Don't delete row 1

 

[Porky79]

Instead of using a macro why not just modify the Print_Area defined name to
=offset($a$1,0,0,counta($a:$a),3)
where it will now self adjust. Don't delete row 1

Hi I have tried this - however it seems to set the same print area as
ActiveSheet.PageSetup.PrintArea = "$A$1:$C$52"
i.e. Only selects columns A-C even if there is data in D and onwards.
I am assuming this is because the final number in the OFFSET refers to
the number of columns along from the reference i.e. 3?

cheers

 

[Don Guillett]

I misread to do for ROWS. Change the offset formula for columns. Assumes
something in each cell of row 1

=offset($a$1,0,0,52,counta($1:$1))