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

#### Microsoft® Excel®: Beyond the Basics

LIVE ONLINE SEMINARS

Dec. 3

Eastern Time Zone

Dec. 7

Central Time Zone

Dec. 8

Central Time Zone

Dec. 9

Eastern Time Zone

Dec. 10

Pacific Time Zone

Dec. 14

Central Time Zone

Dec. 15

Pacific Time Zone

Dec. 16

Eastern Time Zone

Dec. 17

Central Time Zone

Jan. 6

Central Time Zone

Jan. 11

Central Time Zone

Jan. 12

Pacific Time Zone

Jan. 14

Eastern Time Zone

Jan. 19

Eastern Time Zone

Jan. 20

Pacific Time Zone

Jan. 21

Central Time Zone

Jan. 25

Central Time Zone

Jan. 27

Central Time Zone

Jan. 28

Eastern Time Zone

Feb. 1

Central Time Zone

Feb. 3

Central Time Zone

Feb. 4

Eastern Time Zone

Feb. 8

Central Time Zone

Feb. 9

Eastern Time Zone

Feb. 11

Central Time Zone

Feb. 15

Central Time Zone

Feb. 17

Eastern Time Zone

Feb. 17

Central Time Zone

Feb. 23

Central Time Zone

Feb. 25

Pacific Time Zone

Mar. 2

Central Time Zone

Mar. 3

Eastern Time Zone

Mar. 8

Central Time Zone

Mar. 10

Central Time Zone

Mar. 15

Central Time Zone

Mar. 17

Central Time Zone

Mar. 18

Eastern Time Zone

Mar. 23

Central Time Zone

Mar. 25

Central Time Zone

Mar. 29

Eastern Time Zone

Mar. 30

Central Time Zone

Apr. 1

Pacific Time Zone

#### Microsoft® Excel® Basics

LIVE ONLINE SEMINARS

Dec. 6

Central Time Zone

Dec. 7

Central Time Zone

Dec. 8

Eastern Time Zone

Dec. 9

Pacific Time Zone

Dec. 13

Central Time Zone

Dec. 14

Pacific Time Zone

Dec. 15

Eastern Time Zone

Dec. 16

Central Time Zone

Jan. 5

Central Time Zone

Jan. 10

Central Time Zone

Jan. 11

Pacific Time Zone

Jan. 13

Eastern Time Zone

Jan. 18

Eastern Time Zone

Jan. 19

Pacific Time Zone

Jan. 20

Central Time Zone

Jan. 24

Central Time Zone

Jan. 26

Central Time Zone

Jan. 27

Eastern Time Zone

Jan. 31

Central Time Zone

Feb. 2

Central Time Zone

Feb. 3

Eastern Time Zone

Feb. 7

Central Time Zone

Feb. 8

Eastern Time Zone

Feb. 10

Central Time Zone

Feb. 14

Central Time Zone

Feb. 16

Eastern Time Zone

Feb. 16

Central Time Zone

Feb. 22

Central Time Zone

Feb. 24

Pacific Time Zone

Mar. 1

Central Time Zone

Mar. 2

Eastern Time Zone

Mar. 7

Central Time Zone

Mar. 9

Central Time Zone

Mar. 14

Central Time Zone

Mar. 16

Central Time Zone

Mar. 17

Eastern Time Zone

Mar. 22

Central Time Zone

Mar. 24

Central Time Zone

Mar. 28

Eastern Time Zone

Mar. 29

Central Time Zone

Mar. 31

Pacific Time Zone

#### Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

LIVE ONLINE SEMINARS

Dec. 3

Central Time Zone

Dec. 8

Central Time Zone

Dec. 9

Central Time Zone

Dec. 14

Eastern Time Zone

Dec. 15

Central Time Zone

Jan. 7

Central Time Zone

Jan. 12

Eastern Time Zone

Jan. 13

Central Time Zone

Jan. 18

Central Time Zone

Jan. 24

Central Time Zone

Jan. 26

Pacific Time Zone

Feb. 2

Eastern Time Zone

Feb. 4

Central Time Zone

Feb. 8

Pacific Time Zone

Feb. 9

Central Time Zone

Feb. 15

Eastern Time Zone

Feb. 18

Central Time Zone

Feb. 24

Central Time Zone

Mar. 3

Central Time Zone

Mar. 8

Eastern Time Zone

Mar. 11

Central Time Zone

Mar. 16

Pacific Time Zone

Mar. 21

Central Time Zone

Mar. 23

Eastern Time Zone

Mar. 31

Central Time Zone

#### Monthly Archives

• سینا says:

thanks for the good explanation

• Mike says:

Great. This works fantastic. Thanks.

• Abhi says:

Excellent!
Just what I needed

• Jon McMahan says:

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

• Excel Tips and Tricks from Pryor.com says:

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.