Areas Collection

This example illustrates the Areas collection in Excel VBA. Below we have bordered Range("B2:C3,C5:E5"). This range has two areas. The comma separates the two areas.



Place a command button on your worksheet and add the following code lines:

1. First, we declare two Range objects. We call the Range objects rangeToUse and singleArea.

Dim rangeToUse As Range, singleArea As Range

2. We initialize the Range object rangeToUse with Range("B2:C3,C5:E5")

Set rangeToUse = Range("B2:C3,C5:E5")

3. To count the number of areas of rangeToUse, add the following code line:

MsgBox rangeToUse.Areas.Count

Result:



4. You can refer to the different areas of rangeToUse by using the index values. The following code line counts the numbers of cells of the first area.

MsgBox rangeToUse.Areas(1).Count

Result:



5. You can also loop through each area of rangeToUse and count the number of cells of each area. The macro below does the trick.

For Each singleArea In rangeToUse.Areas
    MsgBox singleArea.Count
Next singleArea

Result:





For a practical example of the areas collection, see our example program Compare Ranges..