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

 

[Simon Lloyd]

This will find the last used column and create your printa area
accordingly!

Sub Set_PrintArea_lastused_column()
Dim r As String
Dim ColNumb As Integer, ColLet As String
ColNumb = ActiveSheet.UsedRange.Column
If ColNumb > 26 Then
ColLet = Chr(Int((ColNumb - 1) / 26) + 64) & _
Chr(((ColNumb - 1) Mod 26) + 65)
Else
ColLet = Chr(ColNumb + 64)
End If
ActiveSheet.PageSetup.PrintArea = "$A$1:$" & ColLet & "$52"
End Sub

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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[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))