Areas Collection in Excel VBA

 

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

Areas Collection in Excel VBA

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:

Count Areas

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:

Count Cells, First Area

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:

Count Cells, First Area

Count Cells, Second Area

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

13/14 Completed! Learn more about the range object ➝
Go to Next Chapter: Variables