# 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
```