How to Loop Through All Cells and Multiple Ranges in VBA?

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.

Code:

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

Explanation:

• 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.

Example:

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.

 

Questions? Comments? Feel free to leave us a note below!