Break out of or Exit Different Types of Loops in VBA Macros in Excel

How to Exit, End, or Break out of loops in Excel.

This tutorial includes breaking out of While, Do, and For loops.

Sections:

Break out of a Do Loop

Break out of a For Loop

Break out of a While Loop

Break out of a Loop when a Condition is Met

Notes

Break out of a Do Loop

This will exit or break out of a Do loop, including Do While and Do Until loops. When this occurs, script execution will continue after the current loop. If there are Do loops nested within Do loops, only the one directly containing the Exit statement will be broken out of and execution of the script will continue with the next containing Do loop.

Exit Do

Simply put this in the place where you want to exit the loop.

Break out of a For Loop

This will exit or break out of a For loop, which includes a For...Next loop and a For Each...Next loop. When this occurs, script execution will continue after the current loop. If there are nested For loops, only the one directly containing the Exit statement will be broken out of and execution of the script will continue with the next containing For loop.

Exit For

Simply put this in the place where you want to exit the loop.

Break out of a While Loop

This will exit or break out of a While loop. When this occurs, script execution will continue after the current loop. If there are While loops nested within While loops, only the one directly containing the Exit statement will be broken out of and execution of the script will continue with the next containing While loop.

Exit While

Simply put this in the place where you want to exit the loop.

Break out of a Loop when a Condition is Met

Now you know the simple syntax for exiting a loop, but it's not useful without a condition.

Include the Exit statement within an IF condition. Use the IF condition to check if it is time to exit from the loop or not.

Here is a very basic loop example along with a condition that checks from when the loop needs to be canceled or exited.

Sub Loop_Death()

For i = 1 To 10

    'show some output
    MsgBox i

    If i = 5 Then

        'exit the loop
        Exit For

    End If

Next i

End Sub

Here it is in Excel for better reference:

Here, I made a simple For loop that should run 10 times. However, I placed an IF statement that checks when the variable i has reached 5 and, when this happens, the Exit For line of code will run and the For loop will be broken out of or exited and, since there is no other loop containing this loop or any code after the loop, there is nothing else for the macro to do and so its execution will end.

I included a msgbox output that will display the value contained within the variable i, which will just be the number of times the loop has run since that variable is incremented each time the loop runs. This feature is there just to help you visualize the exit from the For loop.

You can also use a one-line IF statement like this to streamline the code:

Sub Loop_Death()

For i = 1 To 10

    'show some output
    MsgBox i

    If i = 5 Then Exit For

Next i

End Sub

Notes

This is a basic feature of VBA and Macros but it is very important and it will help you add a lot of control to your macros. Make sure to memorize these three simple Exit statements for the loops.

There is no Exit statement for a While Wend loop. If you need to exit one of these loops, simply change it to a Do While or Do Until loop.

Don't forget to download the Excel file that accompanies this tutorial and test this macro out.