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 the cells in a row
- Loop the columns in a range
- Loop the rows in a range
- Loop 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:
$B$2:a, $C$2:b, $B$3:1, $C$3:2
.
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 1, 2
.
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 a, 1, 2
.
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 2, 3
.
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 2, 3, 4
.
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 $B$2:$C$3,$F$2:$F$3
.
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