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

Add to Favorites

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:

3400f030ce7363c7e00549c99c10b8af.jpg

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

938fd637564a37da7fba562401a712e7.jpg

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.


Downloadable Files: Excel File

Similar Content on TeachExcel
Loop through a Range of Cells in a UDF in Excel
Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Create a Column Chart with a Macro in Excel
Macro: This macro adds a column chart to Excel. This is an easy to use macro that allows you to q...
Show All Formulas in a Worksheet in Excel
Tutorial: Display all formulas instead of their output values. This allows you to quickly troubles...
Highlight and Sort the Top and Bottom Performers in a List in Excel
Tutorial: How to highlight the rows of the top and bottom performers in a list of data. This allow...
Highlight Every Other Row in a Selection in Excel - Table Formatting
Macro: This free Excel macro will highlight every other row in a selection of cells with a co...