Page 13 of 24PREVNEXT

Get in the loop with Excel macros

Nested loops contain other loops

A loop inside a loop.

The Do…Loop and the For Each…Next loop are powerful in simple ways. Now we're going to ratchet up the complexity a bit by introducing nested loops. You use nested loops when you need to perform an action on a range of data more than once, or through more than one range of data. For an analogy to nested loops, think of the Earth revolving around the Sun. One full revolution around the Sun, a year, is like the outside loop, and one rotation of the Earth around its axis, a day, is like the inside loop nested within the outside loop. For each year, there are 365 inside loops, and on every January 1st, the outside loop repeats:

Do While (Earth rotates around axis)

OK, so the code above wouldn't actually run in Excel, but it illustrates that for each big loop (around the sun) there are 365 smaller loops (around Earth's axis).

Or consider a process that would actually run in Excel. Suppose you wanted to remove duplicate rows from a worksheet. You could compare the first row with every row after it, find any duplicates, and delete them. Then you'd compare the second row to every row after it, and so on, and so on. The outside loop would make the process repeat for each row. The inside loop would do the actual comparing and deleting.

In the next lesson, you'll see exactly how to make that happen.

Page 13 of 24PREVNEXT