Excel VBA: How to Exit a For Loop (5 Examples)

Excel VBA is a powerful tool that can automate tasks and make complex calculations in Microsoft Excel. One of the essential features of VBA is the ability to use loops to execute a set of instructions repeatedly. However, sometimes you may need to exit a for loop or any other loops prematurely. There are several ways to exit a loop in VBA, including using the “Exit For” statement, the “Exit Do” statement, the “Exit Sub” statement, etc. In this article, we will show you how to exit a for loop with Excel VBA.

There are several advantages and disadvantages to exiting a loop prematurely in VBA. Let’s take a closer look at each:

How to Launch Visual Basic Editor in Excel

To Launch Visual Basic Editor, we need to click on Visual Basic under the Developer tab.

Then, Insert a module to write the code. Finally, inside the module, we can write suitable codes to get our desired output. We have to repeat this process for adding each of the new Macros. (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon)

Overview of VBA For Loop

A VBA For loop is a control structure that allows you to execute a block of code repeatedly for a specified number of times. The For loop is used when you know the exact number of times the code needs to be executed. The basic syntax of a For loop in VBA is as follows:

For counter = start To end [Step increment]
    'code to be executed
Next counter

Here, the counter is the loop variable that is used to control the loop, start is the initial value of the loop variable, end is the final value of the loop variable, and the increment is the amount by which the loop variable is incremented after each iteration. If the Step clause is not specified, the loop variable is incremented by 1.

Inside the loop, you can put any code you want to execute, such as manipulating data, formatting cells, or calling other functions. The loop continues to execute until the loop variable reaches the final value specified in the loop or until the loop is exited prematurely using a control statement like Exit For.

The VBA For loop is a powerful tool that can help automate repetitive tasks, improve the efficiency of your code, and make your macros more flexible and dynamic. It is important to use proper error handling and control statements to ensure that your loops execute correctly and do not cause errors or unexpected behavior in your Excel workbook.

Overview of Exit For Statement

The “Exit For” statement is used to prematurely exit a “For” loop in VBA. When the “Exit For” statement is executed within a “For” loop, the control immediately moves to the statement that follows the “Next” statement. This allows you to exit the loop without having to wait for the loop to complete all its iterations.

The “Exit For” statement is useful when you need to exit a loop under certain conditions. For example, you may need to exit a loop when a specific value is found or when a particular condition is met. By using “Exit For“, you can exit the loop as soon as the condition is met, which can save a lot of processing time.

It is important to note that “Exit For” should be used with caution, and only when necessary. If used incorrectly, it can cause unexpected results or errors in your code. Additionally, there are other ways to exit a loop in VBA, such as “Exit Do” or “Exit While“, which may be more appropriate depending on the type of loop and the situation. Following is a simplified flow chart regarding the use of Exit For Statement.

Excel VBA to Exit a For Loop: 5 Practical Examples

To, demonstrate these 5 examples we have taken a dataset named “Mid Term Results of 6th Grade Students” where the marks of 10 different students in 3 different subjects are visible.

1. Exit a Loop Early

In this example, we are going to create a For Loop to go through all the rows of the dataset. And exit the loop early, if the loop reaches row 7 (Alex).

Steps:

• First, we need to insert a module as stated earlier.
• Then, we need to write the following code inside the module.

Code Syntax: 

Sub EarlyExitExample()
    Dim row As Integer
    For row = 5 To 14 ' assuming the data starts at row 5 and ends at row 14
        If row = 7 Then
        Range("D" & row).End(xlToLeft).Select
        Selection.Interior.ColorIndex = 35
        ' exit the loop when we reach row 7
            Exit For ' early exit without meeting a condition statement
        End If
        ' put any code you want to execute inside the loop
         Next row
    MsgBox "Processing row " & row
End Sub

Consequently, we need to run the code. And you will see how to exit a for loop with Excel VBA.

Finally, you will get the output. Inside the MsgBox, click OK to end the process.

2. Exit a Loop When Condition Met

In this example, the code is going to go through the “Marks” column and search for a perfect score, which is 100. If a perfect score is found, the loop exits and returns the name of the student who achieved it through a MsgBox and highlights the corresponding cell. Follow these steps to exit a for loop with Excel VBA.

Steps:

• First, we need to insert a module as stated earlier.
• Then, we need to write the following code inside the module.

Code Syntax: 

Sub ExitForExample()
    Dim row As Integer
    For row = 5 To 14 'assuming the data starts at row 5 and ends at row 14
        If Range("D" & row).Value = 100 Then 'assuming "Marks" column is in column D
        Range("D" & row).End(xlToLeft).Select
        Selection.Interior.ColorIndex = 35
        MsgBox "Student " & Range("B" & row).Value & " got a perfect score!"
            Exit For 'exit the loop when a perfect score is found
        End If
    Next row
End Sub

Consequently, we need to run the code.

Finally, you will get the output. Inside the MsgBox, click OK to end the process.

Read More: Excel VBA to Use For Loop with Two Variables

3. Exit a For Each Loop

This code is a VBA macro that iterates through a range of cells (B5 to D14), and checks if the value of each cell matches the string “Lily”. If a match is found, the cell’s color is changed, and the loop is exited. The last cell that was checked is used to display a message box, showing the value of the cell and the value of the cell in the next column (in this case, the cell to the right of the matched cell).

Steps:

• First, we need to insert a module as stated earlier.
• Then, we need to write the following code inside the module.

Code Syntax: 

Sub ExitForEachExample()
    Set Rng = Range("B5:D14")
    For Each cell In Rng  ' assuming the data range is B5:D14
        If cell.Value = "Lily" Then
        cell.Select
        Selection.Interior.ColorIndex = 35
        End If
        If cell.Value = "Lily" Then ' exit the loop if we find the name "Lily"
        Exit For
        End If
       ' put any code you want to execute inside the loop
    Next cell
     MsgBox "Processing " & cell.Value & " in " & cell.Offset(0, 1).Value
End Sub

Consequently, we need to run the code.

Finally, you will get the output. Inside the MsgBox, click OK to end the process.

Read More: How to Continue Excel VBA For Loop

4. Exiting a For Loop Using GoTo Statement

This VBA code searches for the name “Alex” in the range of cells from B5 to B14. Once it finds the name, it jumps to a label called “exitLoop“. It then loops through a new range of cells, B5 to D14, and changes the color of any cell that contains the name “Alex”. Finally, it displays a message box that shows the name and location of the cell containing the name “Alex” which caused the loop to exit.

Steps:

• First, we need to insert a module as stated earlier.
• Then, we need to write the following code inside the module.

Code Syntax:

Sub ExitForLoopWithGoto()
    Dim i As Integer
    For i = 5 To 14 ' assuming the data range is in rows 5 to 14
        If Range("B" & i).Value = "Alex" Then ' exit the loop if we find the name "Alex"
        GoTo exitLoop
        End If
        ' put any code you want to execute inside the loop
    Next i
exitLoop:
  Set Rng = Range("B5:D14")
    For Each cell In Rng  ' assuming the data range is B5:D14
        If cell.Value = "Alex" Then
        cell.Select
        Selection.Interior.ColorIndex = 35
        End If
        Next cell
MsgBox "Processing " & Range("B" & i).Value & " in " & Range("C" & i).Value
End Sub

Consequently, we need to run the code.

Finally, you will get the output. Inside the MsgBox, click OK to end the process.

Read More: Excel VBA: For Loop Skip to Next Iteration

5. Early Exit of a  For Loop by Changing Loop Counter

In this example, the code checks if the value “Ryan” is present in column B of rows 5 to 14 and exits the loop early if found. Then, it sets the background color of cells containing “Ryan” to index 35 and displays a message box with the value in cells B9 and C9.

Steps:

• First, we need to insert a module as stated earlier.
• Then, we need to write the following code inside the module.

Code Syntax:

Sub EarlyExitForLoopCounter()
    Dim i As Integer
    For i = 5 To 14 ' assuming the data range is in rows 5 to 14
        If Range("B" & i).Value = "Ryan" Then ' exit the loop if we find the name "Ryan"
            i = 15 ' set the loop counter to a value outside the loop range to exit early
        Else
            ' put any code you want to execute inside the loop
        End If
    Next i
     Set Rng = Range("B5:D14")
    For Each cell In Rng  ' assuming the data range is B5:D14
        If cell.Value = "Ryan" Then
        cell.Select
        Selection.Interior.ColorIndex = 35
        End If
        Next cell
 MsgBox "Processing " & Range("B" & 9).Value & " in " & Range("C" & 9).Value
End Sub

Consequently, we need to run the code.

Finally, you will get the output. Inside the MsgBox, click OK to end the process.

Until now, we have seen different examples of prematurely break a For Loop. Now, it is time to learn how to exit/break other types of VBA Loops. Such as Do-Until Loop, Do-While Loop, and Infinite Loop.

How to Exit/ Break Do-Until Loop in Excel VBA

In this section, the code uses a Do Until loop to iterate through numbers 1 to 10 and breaks the loop when i is equal to 7 using the Exit Do statement. It then selects cells in a range and sets their interior color to index 35 if their value is “Alex”. Finally, it displays a message box showing the row number and student name for the current value of i.

Steps:

• First, we need to insert a module as stated earlier.
• Then, we need to write the following code inside the module.

Code Syntax:

Sub DoUntilLoop()
    Dim i As Integer
    i = 1
    Do Until i > 10
        If i = 7 Then 'Break the loop when i is equal to 7
            Exit Do
        Else
            i = i + 1
        End If
    Loop
     Set Rng = Range("B5:D14")
    For Each cell In Rng  ' assuming the data range is B5:D14
        If cell.Value = "Alex" Then
        cell.Select
        Selection.Interior.ColorIndex = 35
        End If
        Next cell
        'Print the row number and student name
        MsgBox "Row " & i & ": " & Cells(i, 2).Value
End Sub

Consequently, we need to run the code.

Finally, you will get the output. Inside the MsgBox, click OK to end the process.

How to Break a Do-While Loop in Excel VBA

This code is an example of a Do While loop. It sets a worksheet and loop variable and checks the marks in the “Marks” column of the worksheet. If the marks are greater than 95, the loop is exited, otherwise, the loop continues. Some actions can be performed inside the loop. Finally, the code changes the background color of any cell with a value greater than or equal to 95 in a predefined range and displays a message indicating that the loop has exited.

Steps:

• First, we need to insert a module as stated earlier.
• Then, we need to write the following code inside the module.

Code Syntax:

Sub DoWhileLoopExample()

    Dim ws As Worksheet
    Dim i As Long
   
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'Change "Sheet1" to the name of your worksheet
   
    i = 5
    Do While i <= 15
        If ws.Cells(i, 4).Value > 95 Then 'Change 4 to the column number of "Marks"
            Exit Do
        Else
            i = i + 1
        End If
         'Perform any actions inside the loop here
    Loop
     Set Rng = Range("D5:D14")
    For Each cell In Rng  ' assuming the data range is B5:D14
        If cell.Value >= 95 Then
        cell.Select
        Selection.Interior.ColorIndex = 35
        End If
        Next cell
 MsgBox "Marks value exceeds 95. Exiting loop now."
End Sub

Consequently, we need to run the code.

Finally, you will get the output. Inside the MsgBox, click OK to end the process.

What Is an Infinite Loop in Excel VBA?

An infinite loop in Excel VBA is a loop that runs indefinitely because the loop condition is never met or because the code inside the loop is not allowing the loop to exit. This can cause the program to freeze or crash and can lead to loss of data. It’s important to ensure that any loops in Excel VBA have a clear exit condition and to test the code thoroughly to avoid infinite loops. Below is an example of an infinite Do-While Loop:

This code creates an infinite loop in VBA that displays a message box repeatedly. The loop will run indefinitely unless the user manually stops it by pressing Ctrl + Break on the keyboard.

How to Break an Infinite Loop or Any VBA Loop with Keyboard in Excel

Breaking a loop using the keyboard is important because it allows the user to stop an infinite or long-running loop that may otherwise freeze or crash the program or the computer. By pressing the Ctrl + Break keys, the user can interrupt the loop and stop the execution of the code, preventing any potential harm or damage to the system. It is, therefore, important for programmers to include ways to break loops in their code and for users to be aware of how to do so in case they encounter an infinite or long-running loop. Below is an example:

Best Practice While Using a Loop in Excel VBA

In VBA, it is important to anticipate and handle errors that might occur during the execution of a loop. This is because a single error can cause the entire loop to terminate prematurely or produce incorrect results. One common way to handle errors is to use the “On Error” statement, which allows the user to define a specific error-handling routine. In this routine, the user can use various VBA statements to address the error, such as displaying a message box or logging the error to a file. By implementing proper error-handling techniques, it can be ensured that a loop runs smoothly and handles any errors that may occur. Below is an example:

You can also see the output image:

Advantages of Loops in Excel VBA

• Saves time and resources: When you exit a loop prematurely, you save time and computational resources that would have been used to complete the remaining iterations of the loop.
• Increases efficiency: By exiting a loop prematurely when a specific condition is met, you can increase the efficiency of your code and improve its performance.
• Improves readability: Exiting a loop prematurely can also improve the readability of your code by eliminating unnecessary code that would have been executed if the loop continued to its natural conclusion.

Disadvantages of Loops in Excel VBA

• Incomplete execution: Exiting a loop prematurely means that some iterations of the loop will not be executed. This can lead to incomplete data analysis or calculations, potentially compromising the accuracy of your results.
• Unexpected behavior: If you’re not careful, prematurely exiting a loop can lead to unexpected behavior in your code, especially if you have nested loops or other code that depends on the loop to complete its execution.
• Difficult to debug: Exiting a loop prematurely can make it difficult to debug your code, as it can be challenging to determine why the loop terminated before it was supposed to.

Overall, it’s important to carefully consider the advantages and disadvantages of exiting a loop prematurely in VBA before implementing this strategy in your code. While it can be a useful technique in some cases, it’s essential to ensure that your code remains accurate, efficient, and easy to debug.

In this article, we’ll explore the loop-exiting methods of VBA in-depth. We’ll also provide examples of how to exit a loop in VBA and discuss best practices for using these statements, including error handling and avoiding infinite loops. By the end of this article, you’ll have a thorough understanding of how to exit a loop in VBA and which method is best for your specific use case.

Download Practice Workbook

You can download this workbook.

Conclusion

Exiting a loop in Excel VBA is a crucial skill for any programmer. Infinite loops can cause Excel to become unresponsive, and can also lead to data loss. To avoid these issues, it’s important to use best practices when working with loops. These include error handling and using the appropriate loop structure for the task at hand. Additionally, there are several ways to exit a for loop in Excel VBA, including using the Exit For statement, changing the loop counter, or using the Ctrl + Break shortcut. By implementing these best practices and techniques, programmers can ensure that their code is efficient, reliable, and easy to maintain. I hope this article has provided you with enriched ideas to exit a for loop with Excel VBA.

Related Articles

• Excel VBA: Running a Backwards For Loop
• Excel VBA with For Loop Decrement
• Excel VBA For Loop with Array
• How to Use Excel VBA Nested For Loop