Combine Multiple Ranges

You can use the Union method to create a muliple area range. A multiple area range contains more than one block of cells, which may not be connected. For example, you use the Union method to create a Range object containng the cells A1 through B5 and D1 through E5. Although these two groups of cells are separated within the worksheet, using the Union method you can create one range that references only those cells.

When you use the Range property in conjunction with the Union method, you can assign any number of parameter values, as long as you specify at least two different ranges. Each parameter value must specify a range of cells. You can specify the ranges that you assign to the Union method using any option that returns a valid Range object, such as the Range property or the Cells property. See the sections "Using the Range Property" and "Using the Cells

Property" for more information on the Range and Cells properties.

For example, the code Set RangeVar = Union (Range("A1:A3"), Range("A5:A15") uses the Union method to combine two Range objects created with the Range property and assigns the result to a Range variable. With this sample code, the new range contains the cells A1 through A3 and A5 through 15. When you view this range, you see that cell A4 is not selected as part of the range.

Because you must declare the variable to which you assign the multi-area range as a Range object, you need to use the Set statement as part of the assignment statement. You must use the Set statement whenever you assign an object to a variable. See Chapter 4 for more information on assigning objects.



□ Create a new subroutine.

□ Type Set Range2 = Cells(5,5), replacing Cells(5,5) with a reference to the second range.

□ Create a new subroutine.

ype Dim Range1 As Range, replacing Rangel with the name of the first range.

< Type Dim Range2 As Range, replacing Range2 with the name of the second range.

_Q Type Dim NewRange As Range, replacing NewRange with the name of the combined range.

Q Declare any additional variables needed for the subroutine.

L-0 Type Set Range1 = Range("A1:B3"), replacing Range("A1:B3") with a reference to the first range.

□ Type Set Range2 = Cells(5,5), replacing Cells(5,5) with a reference to the second range.

0 0


Post a comment