Table of Contents
Areas Collection
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.
Dim rangeToUse As Range Dim 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 number of cells in 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 in each area. The macro below does the trick.
For Each singleArea In rangeToUse.Areas
MsgBox singleArea.Count
Next singleArea
Result:
