PrintArea

[avpendse]

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.

 

[STEVE BELL]

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

 

[Trevor Shuttleworth]

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

 

[avpendse]

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.

 

[STEVE BELL]

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