When you need to work with ranges that are not connected (contiguous), use the Areas collection.
A Range contains an Areas collection containing one or more Ranges.
Most commonly it’s a Selection containing Areas.
Hold down the Control key and select cells A1:A5 and C1:C5
The highlighted cells are represented by the Selection object.
The Selection object has an Areas collection.
Item 1 of Areas is the range A1:A5
Item 2 of Areas is the range C1:C5
Because items of Areas are just Ranges, you can do operations like:
One of the common uses of Areas is the Worksheet SelectionChange event.
The user can create any sort of Selection so handling Areas is a good idea.
This example writes to the status bar as you select cells.
Dim str As String, rng As Range
For Each rng In Target.Areas
str = str & IIf(str = “”, “”, “, “) & rng.Address(False, False)
Application.StatusBar = “Selected Areas: “ & str
To create a range with Areas, use Union.
When you Union two ranges together, a range is returned which contains an Areas collection with two range items.
Dim rng1 As Range, rng2 As Range, rngSuper As Range
Set rng1 = Range(“A1:A5”)
Set rng2 = Range(“C1:C5”)
Set rngSuper = Union(rng1, rng2)
Exception: If the Union ranges create a contiguous range it gets treated as a single Area.
Try the code but instead of C1:C5 use B1:B5