celebrity writers

How to Loop in Cell Range in Excel

Three Ways to Iterate Over Ranges Using MS Visual Basic for XL

Mar 22, 2007

This Excel VBA programming tutorial article takes the reader through three ways to loop through cell ranges. It covers counted, uncounted and user defined loops.

Introduction

There are many reasons to want to loop through ranges of cells in an Excel worksheet. The most common seem to be:

Changing cell color based on a condition;
Creating sets of data;
Generating reports.

Programmers will be aware that loops enable us to tackle the movement through an array of objects in a variety of ways. We can use a counted loop to iterate a set number of times, or an uncounted loop which will only stop once a certain condition is met.

In this article, we will look at how to loop through a collection of cells using both counted and uncounted loops. The term range is used here to identify an object containing cells from a worksheet.

Looping with a For (Each) Loop

The easiest way to loop through a range of cells is to use a For Each loop. For example, the following code snippet loops through the rows in a worksheet:

For Each rwRow In Worksheets(0).Rows
' Do things with the rwRow object
Next rwRow

We can also use a standard For loop and two numerical variables in a standard nested loop, in order to access the cells in a range as if they were in a two dimensional array:

For rwNumber = 1 To 100
For clNumber = 1 To 50
' Cell can be obtained through
' Worksheets(0).Cells(rwNumber, clNumber)
Next clNumber
Next rwNumber

We can of course combine the two in order to loop through the Row objects, and then access cells through the appropriate property:

For Each rwRow in Workshets(0).Rows
For clNumber = 1 To rwRow.Columns.Count
' Cell can be obtained through
' Row.Cells(1, clNumber)
Next clNumber
Next rwNumber

These solutions only work when we actually know what area of cells are affected. However, in some cases we might like to be able to test for a delimeter, or a cell value where we should stop iterating. Excel provides a solution using uncounted loops.

Looping with a While Loop

A While loop is designed to repeat until a value is met. This value is evaluated at the start of each iteration. We need to set up any variables that are used within the loop, or within the condition, outside of the main While loop.

The following code snippet tests for an empty cell, and stops when it is reached:

nCol = 1
nRow = 1
While Not IsEmpty(Worksheets(0).Cells(nRow, nCol))
' Process the cell here
' Update also nRow and/or nCol
Wend

As the reader will see from the little comment in the code, it is vital that the nRow or nCol indexes are updated, otherwise the code will be stuck in an endless loop. If both indexes are updated, then the movement though the cells will perhaps not be what was intended. To move through the range as if it were an array, we would need to nest the While loops.

We can also use a Do loop to achieve the same result, but if we do that, then we need to provide an exit clause within the loop itself. The following code snippet shows this in practice:

nCol = 1
nRow = 1
Do
If IsEmpty(Worksheets(0).Cells(nRow, nCol)) Then
Exit Do
End If
' Process the cell here
' Update also nRow and/or nCol
Wend

The reader will also note that the condition is now positive, as we are taking action as a result, rather than negative as in the While loop example. We can also nest the Do loop to obtain an array-reference style effect.

Links

Now that the reader can loop through the cells, perhaps they would like to do something with the cell in question:

The copyright of the article How to Loop in Cell Range in Excel in Computer Programming is owned by Guy Lecky-Thompson. Permission to republish How to Loop in Cell Range in Excel in print or online must be granted by the author in writing.