Print Noncontinguous Range with VBA

[mllex]

Can anyone offer thoughts on how to make the printarea property work for a noncontinguous range?

Dim r1, r2, r3, r4, r5, r6, r7, r8, r9, myMultipleinRange As Range
Set r1 = Sheets("in&quot.Range("l274:l500&quot
Set r2 = Sheets("in&quot.Range("d274500&quot
Set r3 = Sheets("in&quot.Range("c274:c500&quot
Set r4 = Sheets("in&quot.Range("d274:d500&quot
Set r4 = Sheets("in&quot.Range("w274:w500&quot
Set r5 = Sheets("in&quot.Range("o274500&quot
Set r6 = Sheets("in&quot.Range("p274500&quot
Set r7 = Sheets("in&quot.Range("q274:q500&quot
Set r8 = Sheets("in&quot.Range("h274:q500&quot
Set r9 = Sheets("in&quot.Range("j274:j500&quot
Set myMultipleinRange = Union(r1, r2, r3, r4, r5, r6, r7, r8, r9)

'Sheets("in&quot.PageSetup.PrintArea = "myMultipleinRange"

I get an error message on the last line.

TIA

 

[DaleWatson123321]

TIA,

My experience has been that even if you were to get your VBA code to accept that last line, you would STILL be faced with a "bug" in Excel - one that automatically inserts a "page-break" between each of the ranges.

If the page-breaks do NOT matter, then of course NO problem.

However, if you DON'T want page-breaks, OR if you want to consider an "alternative approach" (in case the exclusive VBA route doesn't work) - then might I suggest you read my FAQ - faq68-1161 - How To Print Disjointed Ranges on ONE Page.

Another suggestion... Your code suggests you might not be in the habit of using Range Names. If this is the case, I would STRONGLY recommend you begin. They can be EXTREMELY helpful - because "internally" Excel maintains a connection between the names and their cell coordinates.

This means that you can use these names in your code and NOT have to worry about subsequent insertion of rows or columns, or moving data from place to place, etc.

Creating Range Names is as easy as:
1) Highlight the cell or range of cells
2) Hold down <Control> and hit <F3>
3) Type the name
4) Hit <Enter>

In creating Range Names, avoid using names which conflict with Excel's cells or with numbers - i.e. don't use E14 - instead use E_14 or _E14. Also avoid using names which conflict with VBA &quot;reserved names&quot; such as Worksheet, Range, Database, Criteria, etc. The &quot;easy&quot; way to avoid a problem is to use an UNDERSCORE - e.g. &quot;Worksheet_1&quot;.

Hope this will help !!!

Regards, ...Dale Watson
WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca