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:
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.
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
For a practical example of the areas collection, see our example program Compare Ranges.