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:
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.
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.
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.
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.
