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.

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
<[email protected]> wrote in message
news:[email protected]...
>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.
>

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


<[email protected]> wrote in message
news:[email protected]...
>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.
>

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.

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
<[email protected]> wrote in message
news:[email protected]...
> 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.
>