
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 Range2. 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.CountResult:

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).CountResult:

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.AreasResult:
MsgBox singleArea.Count
Next singleArea


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