How to Select the Union of Two or More Specified Ranges

To select the union (that is, the combined area) of the two named ranges "Address" and "Contact," you can use the following example:

Application.Union(Range("Address"), Range("Contact")).Select

Note that both ranges must be on the same worksheet for this example to work. Union method does not work across sheets. For example, this line works fine

Set y = Application.Union(Range("Sheet1!E5:F6"), _   Range("Sheet1!I9:J10"))

but this line

Set y = Application.Union(Range("Sheet1!E5:F6"), _   Range("Sheet2!I9:J10"))

returns the error message:

Union method of application class failed