Using For Each in Excel range

As the image above shows - taken from the Excel VBA menu-(download here...), there are many ways one can specify ranges to loop through. In this article several ways are discussed to do this.
- For Each cell in a range
- Loop through the cells in a row
- Loop through the cells in a column
- Loop through the columns in a range
- Loop through the rows in a range
- Loop through the areas in a range
For Each cell in a range

One of the most common things you will do when programming VBA in Excel is looping though a collection of cells in a specified range, as in the example below which prints the Address and Value of 4 cells on the 'Data' worksheet to the immediate window: .
Dim rng As Range: Set rng = Application.Range("Data!B2:C3")
Dim cel As Range
For Each cel In rng.Cells
With cel
Debug.Print .Address & ":" & .Value
End With
Next cel
Loop through the cells in a row

The code below shows how to loop through the cells in the row with RowIndex:=2
.
Applied to the data in the sheet on the right this will return .
From this we see that rows are counted from the starting point of rng, so the row is 3 on the worksheet, 2 inside rng.
Also, only cells inside the set range rng are taken.
Dim rng As Range: Set rng = Application.Range("Data!B2:C3")
Dim i As Integer
For i = 1 To rng.Rows.Count
Debug.Print rng.Cells(RowIndex:=2, ColumnIndex:=i).Value
Next
Loop through the cells in a column
The code below shows how to loop through the cells in the column with ColumnIndex:=B
.
Applied to the data in the sheet on the right this will return .
From this we see that columns are counted from the starting point of rng, so the column is C on the worksheet, B inside rng.
Also, only cells inside the set range rng are taken.
Dim rng As Range: Set rng =
Dim i As Integer
For i = 1 To rng.Rows.Count
Debug.Print rng.Cells(RowIndex:=i, ColumnIndex:="B").Value
Next
Loop through the columns in a range
The code below shows how to loop through the columns in the Range B2:C4
.
Applied to the data in the sheet on the right this will return .
From this we see that columns are counted from the starting point of the worksheet.
Dim rng As Range: Set rng = Application.Range("B2:C4")
Dim col As Range
For Each col In rng.Columns
Debug.Print col.Column
Next col
Loop through the rows in a range
The code below shows how to loop through the rows in the Range B2:C4
.
Applied to the data in the sheet on the right this will return .
From this we see that rows are counted from the starting point of the worksheet.
Dim rng As Range: Set rng = Application.Range("B2:C4")
Dim col As Range
For Each row In rng.Rows
Debug.Print col.Row
Next row
Loop through the areas in a range

Often we assume a range to have a rectangular shape, but this need not be the case.
The example sheet on the right shows a selection containing two areas: Selection.Address
returns .
Such a situation may also occur as a result of the Intersect
method, or other causes.
To handle the two ranges separately can can pick then from the Areas collection:
Dim rng As Range: Set rng = Application.Selection
Dim rngArea As Range
For Each rngArea In rng.Areas
Debug.Print rngArea.Address
Next rngArea