The “.Areas.Count” property has the number of areas stored in a Range object. You can loop through the “.Areas” in a Range object to access each of the areas in a range individually. This ability is very handy on subroutines or functions designed to perform actions on all cells a user selected, because this accounts for any number of areas a user may select and will function as intended in all circumstances.
Below is an Excel VBA example of code that can loop through all cells in a range regardless of the number of areas.
Sub LoopThroughAllCells() Dim selectedRange As Range Dim area As Range Dim cell As Range Dim areaCount As Integer Set selectedRange = Application.Selection areaCount = 1 Debug.Print "# of Area(s):"; If selectedRange.Areas.Count = 1 Then Debug.Print 1 For Each cell In selectedRange 'loop through each cell in the selected range Debug.Print cell.Address 'do whatever Next Else Debug.Print selectedRange.Areas.Count For Each area In selectedRange.Areas 'more than 1 selected area Debug.Print "Area#: " & areaCount For Each cell In area 'loop through each cell in the selected range Debug.Print cell.AddressLocal 'do whatever Next areaCount = areaCount + 1 Next End If End Sub
- The line If selectedRange.Areas.Count tests for the number of cell areas that has been selected by the user. If the number is 1, then the user only selected 1 area. If not, then the user selected multiple areas.
- If there is only one area, the script directly loops through all the cells in the one range.
- If there are more than one area, the script loops through each of the ranges (as represented by the variable area). Then nested in the for loop, the script loops through each cell individually for the area.
- You can replace the “Debug.Print” line with “do whatever” comment with the action / function you want to perform for each of the cells.
Here is a practical example when running the script with screenshots. A user first selects two cell ranges.
Running the VBA Macro, it detects two selected cell ranges, as you can see with the output “# of Area(s): 2”. And then loops through each of the cells and prints out the cell address of each selected cell.