PrintArea

  • Thread starter Thread starter avpendse
  • Start date Start date
A

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there a limit on the length of a PrintArea? 2
Need help creating a variable size printarea marco. 5
Print Area Range Selection 2
Set print area problem 6
Array formulas + more than 255 chars 4
variable print area? 4
Record Click on Error Indicator 2
Creating print areas automatically using VBA 3

Back
Top