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.

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.

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

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

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.

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.

Result:

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

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.