Set Print Area
 

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

Set Print Area
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Porky79" wrote in message
...
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

Set Print Area
 

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

Porky79;321475 Wrote:
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)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=89863

Set Print Area
 

On 24 Apr, 17:11, "Don Guillett" wrote:
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

Set Print Area
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Porky79" wrote in message
...
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