Page 11 of 24PREVNEXT

Get in the loop with Excel macros

A Do...Loop

A Do…Loop counts rows in a range of data as long as it does not find a blank row.

Suppose you wanted to count the number of rows in a range of data that can sometimes be small and sometimes really big. You'd want to use a Do…Loop. This type of loop performs an action as many times as necessary. It would count whatever number of rows it found in the range. Or suppose you wanted to perform one action on two ranges of data containing different numbers of rows. Again, you'd use a Do…Loop. The loop would run as many times as necessary for each of the two ranges. How does the loop know what's necessary? You tell it. The loop stops running when it meets a specific piece of data, such as a blank line or some particular text.

You use either the While condition or the Until condition to specify when a Do…Loop will stop. As long as something is true, or until something is true, the loop runs. So for a loop that stops when it finds a blank cell in the first column, you would use the While condition, as follows:

Do While Cells(x,1).Value not equal ""

Here the While condition is used so that the loop runs as long as the cell being acted on is not blank. The row being worked on is x, and (x,1) is the first cell in that row. Used together, the signs <> mean "does not equal." The quotation marks with nothing between them indicate a blank cell.

If you wanted the loop to run until it found a cell containing the number 365, you'd use the Until condition. Either way, you tell the loop how to know when it's time to stop.

Page 11 of 24PREVNEXT