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.

 

 

Local Seminars Related to this Topic:

Related Excel® Articles

Categories

Monthly Archives

No Comments

  • سینا 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.

Leave a Reply

Your email address will not be published. Required fields are marked *