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.
5 Comments
thanks for the good explanation
Great. This works fantastic. Thanks.
Excellent!
Just what I needed
I have this macro and want it to repeat on the next column and then the next column…852 columns total… how can I do this?
Sub DuplicateKiller()
Dim N As Long, IR As Long, v As Variant
Dim i As Long, rng As Range
IR = ActiveCell.Column
N = Cells(Rows.Count, IR).End(xlUp).Row
Set r = Range(Cells(1, IR), Cells(N, IR))
For i = N To 1 Step -1
With Cells(i, IR)
v = .Value
If Application.WorksheetFunction.CountIf(r, v) > 1 Then
.ClearContents
End If
End With
Next i
End Sub
It’s possible to cycle through rows and columns in a fairly direct way:
Two FOR loops, one nested within the other will help us out.
The code below will process through 10 rows and 5 columns using the Cells() method and its Value property.
Row 1, Col 1 is A1
Row 3, Col 7 is G3 and so on.
You’d want your inner FOR to loop from 1 to 852.
Sub Button1_Click()
Dim row As Integer
Dim col As Integer
For row = 1 To 10
For col = 1 To 5
Worksheets(“Sheet1”).Cells(row, col).Value = “X”
Next col
Next row
End Sub
This example code only marks each cell with the value “X” but you could place any cacluation within the inner FOR loop.