Excel VBA to Exit For Each Loop (4 Examples)

Introduction to VBA Loops in Excel

Loops are used in VBA (Visual Basic for Applications) to repeatedly perform a series of steps or operations until a certain condition is met.

Types of VBA Loops:

VBA has a variety of loop types, such as For Loops, Do Loops, and While Loops.

For Loop: When the iterative value is known to you, you can use the For Loop. The loops will contain a start value and a stop value. Then it increased by an incremental value. Whenever the condition is met, the For Loop will stop.

Do Loop: The iterative value is unknown, but the condition is known when to stop the loop. There existed 2 types of Do Loops. And they are Do While and Do Until.

While Loop: While loops are similar to the Do loop. Only the start and end statements are different here.

Advantages of Loops in Excel VBA:

• The Loops automatically do your repetitive work through lots of iterations.
• The loops also ensure high accuracy in the code.
• It allows easy modification of the code if any kind of error occurs.
• It handles a large dataset or runs the code with lots of iterations.

Basics of VBA For Each Loop:

Whenever you deal with a collection of items in an array, you may use the “For Each” loop. This loop repeats the set of items from the collection one by one. The collection indicates a table, a worksheet, or a range of cells. The “For Each” loop counts every single item in the loop.

For Each element In group
[ statements ]
Exit For 
[ statements ]
Next [ element ]

The VBA For Each loop has three important sections:

• Item – This is a required field. It is used to iterate over the items in the collection or array.
• Collection – This is the name of the collection or array.
• Statements – The argument you need to execute.

Overview of the Exit For Statement in Excel VBA

An Exit For statement is used when one needs to terminate the iterations. When it is executed, the code stops the iterations and jumps to the next iteration without the condition. We have attached a flow diagram of the “Exit For” statement.

How to Launch the VBA Editor in Excel

• Go to the Developer tab and choose Visual Basic.

• The Visual Basic Editor launches.
• Go to the Insert tab and select Module.

Excel VBA to Exit For Each Loop: 4 Examples

Example 1 – Using the “Exit For” Statement to For Each Loop

If you want to exit the “For Each” loop, you can use the Exit For statement. This statement breaks the loop when a specific condition is fulfilled. In the above code, we declare a variable called myArray where we put 5 fruit names. Then, we iterate it with the For Each loop. The condition we have set here is that when the Item value is “date“, then the code will stop iterating and break code. That’s why you can see the results in the Immediate window as “apple”, ” banana”, and “cherry”.

We have attached the VBA code:

Sub Example()
Dim myArray As Variant
myArray = Array("apple", "banana", "cherry", "date", "elderberry")
Dim Item As Variant
For Each Item In myArray
    If Item = "date" Then
        Exit For ' exit the loop if "date" is found
    End If
    Debug.Print Item
Next Item
End Sub

Example 2 – Use of the “Go To” Statement to Exit a VBA For Each Loop

This breaks the loop when the required situation is met. Here, we use a StopLoop command. When the loop exited in the GoTo statement, the StopLoop showed that the Loop Stopped in the Debug.Print statement. Moreover, the other things are almost the same as Method 1. When the Item is in the For Each loop equals “cherry“. Immediately, the loop stops, and the result is displayed in the Immediate window (see the above image).

Sub GoTo_Statement()
    Dim myArray(3) As String
    myArray(0) = "apple"
    myArray(1) = "banana"
    myArray(2) = "cherry"
    myArray(3) = "mango"
    For Each Item In myArray
        If Item = "cherry" Then
            GoTo StopLoop
        End If
        Debug.Print Item
    Next Item
StopLoop:
    Debug.Print "Loop stopped"
End Sub

Example 3 – Using a Flag Variable to Stop VBA For Each Loop

We have set another variable, “flag” whose initial value is “False“. When the For Each loop starts its iteration from 1 to 15, it checks for the iterative value, whether it is 6 or not. If the value is 6, then the flag variable is “True“. The loop will skip prematurely and stop the iteration. The MsgBox will show the iterative value where the loop has been executed. Moreover, the flag variable here is a boolean variable.

Sub flag_Variable()
Dim i As Integer
Dim flag As Boolean 'taking a new flag variable
    flag = False 'putting the variable FALSE initially
    For i = 1 To 15
        If i = 6 Then
            flag = True 'the variable will be TRUE if condition is met
            Exit For
        End If
    Next i
    If flag = True Then
        MsgBox "Exited the loop occured at i = " & i
        'Showing the result in MsgBox
    Else
        MsgBox "Completed loop"
    End If
End Sub

Example 4 – Applying an “Exit Function” or “Exit Sub” Statement to Exit VBA For Each Loop

You can also use the Exit Sub statement to stop the loop. When you use the Exit For statement, it only stops the For Each loop, but the Exit Sub statement stops the whole sub-procedure immediately after reaching the desired value in the iteration. It stops the loop as well as the procedure. Although it is not encouraged to use it, the whole code will be stopped if any error occurs in the statement.

Follow the VBA code that we have attached here.

Sub Exit_Sub()
    Dim myArray() As Variant
    myArray = Array(1, 2, 3, 4, 5)
    Dim i As Variant
    For Each i In myArray
        If i = 3 Then
            Exit Sub 'Exit the MySub procedure if i is 3
        End If
            Debug.Print i
    Next i
    'Code here that will not be executed if Exit Sub is triggered
End Sub

How to Exit Diverse Types of Loops (Excluding For Each Loop) in Excel VBA

Example 1 – Exit a VBA Do While Loop

For the Do While loop, we have taken the variable i from 1 to 10. If the variable is equal to 5 then the Exit Do command is executed and breaks the iterations.

Sub Do_While_Execution()
    Dim i As Integer
    i = 1
    Do While i <= 10
        ' print the current value of i to the Immediate window
        Debug.Print i
        ' do something here
        If i = 5 Then
            Exit Do ' stop the loop when i is 5
        End If
        i = i + 1
    Loop
    ' print a message to the Immediate window when the loop is stopped
    Debug.Print "The loop has been stopped."
End Sub

Example 2 – Exit a VBA For Next Loop

We used the For Loop to show its execution. The syntax is almost the same. The basic difference is that here we used the Exit For statement for the execution of the iterations.

We have taken the variable p from 5 to 55 and printed the iterative value with the Debug.Print statement. But we want to exit the loop when the value is 10. So, we use the Exit For statement here. The loop will be executed when the value is 10. Also, we use Debug.Print statement to print the execution point and the iterative value.

We have used the following code.

Sub Example_1()
 Dim p 'Variable Declared
For p = 5 To 55 'It will iterate 5 to 55
 Debug.Print p 'Print the Iterative value
 If p = 10 Then
    Exit For 'Exit the Code when condition is met
 End If
Next p
Debug.Print "Exit at " & p
End Sub

Read More: How to Use For Next Loop in Excel VBA

How to Continue a VBA For Loop in Excel

In the image, the value 5 is not printed, as the iterative value of 5 is executed from the code.

In VBA, you cannot use the Continue For a statement. But, there is a GoTo statement that can continue the iterations except for the given condition. The code will stop just for the given condition. For example, if you use a value where the code will exit, then the code will not print that specific value. But the iterations continue, except for that certain value.

The code we have used is:

Sub ExampleCode()
    Dim i As Integer
    For i = 1 To 10
        If i = 5 Then
            'Skip iteration 5 and move on to the next iteration
            GoTo NextIteration
        End If
        'Do something with the current value of i
        Debug.Print i
NextIteration:
    Next i
    'Other code to execute after the loop is finished
End Sub

We take integers 1 to 10 in the variable i. We set a condition. When i=5, then the code will be executed. So, it will not print the value 5 in the Immediate Window. For the execution, we have used the GoTo statement.

Frequently Asked Questions

How do you exit an infinite loop in Excel VBA?

The Infinite loop runs continuously without stopping the loop. If you want to break the loop, press the Ctrl + Break key. Also, you can stop the infinite loop with the Esc key.

When should I use an Exit For statement in Excel VBA?

Whenever your desired condition is fulfilled, and you want to stop the loop, you need to insert the Exit For statement. This will terminate your code prematurely and exit the further iterations when the condition is met.

Can I use the Exit For statement in a nested For loop in Excel VBA?

You can use the Exit For statement in a nested For loop, but the execution will take place in the innermost For loop of the nested loops. If you want to stop all the loops, you need to put Exit For in each loop individually.

Can I use the Exit For statement in a While loop in Excel VBA?

You can use the Exit For statement in the While loop. But instead of the Exit For statement, you can use the Exit While in the While loop.

How does the Exit For statement differ from the Exit Sub statement in Excel VBA?

The Exit For and Exit Sub statements are two different statements. The Exit For statement is used to exit a loop. When it is used, the loop jumps to the For loop and starts the iterations. On the other hand, the Exit Sub statement stops the sub-procedure or function. When it is used, the iteration stops, and the procedure ends.

Things to Remember

• You cannot use the For Each loop in the form of a user-defined type, a user-defined type cannot use in a variant type because it operates on collections of known types. So, it cannot be used as a user-defined type.
• Only a For Loop has the Exit For statement. You will get a build problem if you use it outside a For Loop.
• By eliminating pointless loop iterations, the Exit For statement can improve the efficiency of your code, but it can also make it more difficult to read and comprehend. Only employ it when absolutely required.
• In a For Loop, the Exit For expression can be combined with a Step value.
• After utilizing the Exit For the statement, make sure your code is thoroughly tested to ensure it functions as intended.

Download the Practice Workbook

Related Articles

• Loop through a Range for Each Cell with Excel
• How to Use VBA for Each Row in a Range in Excel
• How to Use VBA for Each Cell in Range in Excel