Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
set area in excel not giving me option to set print area? | Excel Discussion (Misc queries) | |||
File, print area, clear area, is not working | New Users to Excel | |||
print area across the freeze panes area | Excel Worksheet Functions | |||
Macro - Set Print Area for Changing Data Area | Excel Programming | |||
How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) |