How To Use Areas Collection In Excel

Oct 11, 2020 • edited Oct 18, 2020

How To Use Areas Collection In Excel

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.

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.

#Tutorial#How To#VBA

How to Set a range for BeforeDoubleClick (VBA) In Excel

How to Write Data to Text File in Excel