Excel Macro Loop – Limit Redundancy to Save Time and Energy

Does your code need to execute the same action more than once? Instead of programming it multiple times, try writing it once and repeating it with a loop.

Excel offers several different loop structures, depending on the conditions under which it should execute. Check out the table below to see how each loop works and an example of what it might look as driving directions.

Excel Loop

How It Works

How We’d Use It in Real Life

For

Executes a certain number of times

“Turn right three times, then stop.”

Do … Until

Executes until a condition is true, then stops

“Keep going until you cross the railroad tracks, then turn left.”

Do … While

Executes while a condition is true, and stops when it is no longer true

“Keep going while the ditch runs alongside the road, then turn right.”

 

Read on for a description of each of these three types of loops.

For Loop

To repeat a section of code a specific number of times, use a For loop:

Fred Pryor Seminars_Excel Macro Loop 1A

This macro executes the “Cells(i,1).value = i” instruction with i = 1. When it reaches the line “Next i,” the macro increases the value of i to 2 and executes the “Cells” line again. It repeats a third time with i = 3, and finally with i = 4.

The result:

Do Until Loop

Sometimes you don’t know how many times to execute a loop, but instead wish to run the loop until a certain condition is met. To do this, use a Do . . . While loop.

Fred Pryor Seminars_Excel Macro Loop 1B

This code first checks whether the active cell is empty. If not, then it increments the value by one and moves the active cell selector down one row. Finally, it returns to the top of the loop and tests whether the new cell is empty, repeating the loop until it reaches an empty cell.

Beginning with these numbers, if you select cell B3 and begin the macro, it will produce:

Each cell value is increased by one, until the macro reaches cell B13, an empty cell, and stops.

Do While Loop

Similar to the Do . . . Until loop, the Do . . . While loop executes the code while a condition is true.

Fred Pryor Seminars_Excel Macro Loop 1C

The difference between Do . . . Until and Do . . . While is subtle, yet simple:

  • Do . . . Until executes as long as the condition is not true and stops when it becomes true, and
  • Do . . . While executes as long as the condition is true and stops when it becomes false.

Nested Loops

For even more power, try running a loop inside a loop:

This loops through the first 30 rows of the spreadsheet. On each row, it begins in column A and increments the cell value by one until it reaches a blank cell.

Fred Pryor Seminars_Excel Macro Loop 1D

Where To Go from Here

Loops give your code the power to repeat actions. With the variety of loops available in Excel, your code can adapt to any conditions, even when you don’t know ahead of time whether your code needs to run one time or a thousand.