Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PrintArea
I want to set multiple print areas(approx 200 pages) on a worsheet. I
am using - ActiveSheet.PageSetup.PrintArea = ASTRING ("$A$1:$G$18,$A$20:$G$38,$A$40:$G$58,.....AND SO ON".) This results in 1004 error as soon as the length of string "ASTRING" exceeds 255 chars. Any Suggestions. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PrintArea
Sounds like you are going in groups of 18 rows,
where 1st row is 1, than progress to 1 + 19, 1+19 +20, 1+19+20+20 You can use the progression to hide the 19th row of each group and force a page break. You can set this up using a loop. call back if you would like help with the loop... -- steveB Remove "AYN" from email to respond wrote in message oups.com... I want to set multiple print areas(approx 200 pages) on a worsheet. I am using - ActiveSheet.PageSetup.PrintArea = ASTRING ("$A$1:$G$18,$A$20:$G$38,$A$40:$G$58,.....AND SO ON".) This results in 1004 error as soon as the length of string "ASTRING" exceeds 255 chars. Any Suggestions. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PrintArea
You could take out the $ signs which would reduce the number of characters.
You'd be taking out two characters per range ... I don't know if that would be enough ? My testing only got to around 24 pages doing it this way. aString = "A1:G18,A20:G38,A40:G58" aString = aString & ",A60:G78,A80:G98,A100:G118" aString = aString & ",A120:G138,A140:G158,A160:G178" aString = aString & ",A180:G198,A200:G218,A220:G238" aString = aString & ",A240:G258,A260:G278,A280:G298" aString = aString & ",A300:G318,A320:G338,A340:G358" aString = aString & ",A360:G378,A380:G398,A400:G418" aString = aString & ",A420:G438,A440:G458,A460:G478" Debug.Print Len(aString) For Each Sheet In Sheets Sheet.PageSetup.PrintArea = aString Next Alternatively, you could simply hide the rows you don't want to print. Regards Trevor wrote in message oups.com... I want to set multiple print areas(approx 200 pages) on a worsheet. I am using - ActiveSheet.PageSetup.PrintArea = ASTRING ("$A$1:$G$18,$A$20:$G$38,$A$40:$G$58,.....AND SO ON".) This results in 1004 error as soon as the length of string "ASTRING" exceeds 255 chars. Any Suggestions. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PrintArea
Thanks for the suggestion. However setting up the loop is not the
problem and hiding the row (19, 39, ... ) is not desirable. I can set these print areas manually (Select 1st range then Set Print Area , Select 2nd range then Add To Print Area ,.... and so on.) but setting them throug VBA results in error. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
PrintArea
Try this loop (worked in Excel 2000)
[assumes that column A will always have something in the last row] Dim lrw As Long, x As Long, y As Long, z As Long lrw = Cells(Rows.Count, "A").End(xlUp).Row ActiveSheet.Range("a1:e19").PrintPreview x = 20 Do Until x lrw y = x z = y + 18 ActiveSheet.Range(Cells(y, 1), Cells(z, 1)).PrintPreview x = y + 20 Loop -- steveB Remove "AYN" from email to respond wrote in message ups.com... Thanks for the suggestion. However setting up the loop is not the problem and hiding the row (19, 39, ... ) is not desirable. I can set these print areas manually (Select 1st range then Set Print Area , Select 2nd range then Add To Print Area ,.... and so on.) but setting them throug VBA results in error. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PrintArea setting | Excel Programming | |||
PrintArea | Excel Programming | |||
PrintArea | Excel Programming | |||
PRINTAREA | Excel Programming |