Using VBA Exit For Statement to Break a Loop

What is VBA Exit For?

VBA Exit For is a statement you can use within a For Loop to exit the loop before its completion if a condition is met. Let’s say you have a loop to enter serial numbers in a range of cells. With Exit For and IF, you can write a condition to end the loop if any of the cells where you are trying to add serial numbers already have a value.

In the above example, you can see that we have a value in cell A7. You can write a For Loop code with the Exit For to end the loop when it reaches cell A7. But here, we will not specify the cell but the condition to test the value and then the Exit For statement. You can also say that it is like breaking the loop.

Write a Code with VBA Exit For

To write a code which we have discussed earlier, you can use the below steps:

1. First, you need to write a code using the For Loop to enter the numbers in the range A1:A10. The line below the code will enter numbers 1 to 10 in the range A1:A10.
   
2. When you run the above code, it will overwrite the cell value (if there is a value). But as we have discussed, we need to use IF and Exit For to write a condition.
3. For this, with the IF, you need to test whether the cells have value. See the example below; after starting the For Loop, we added a line of code for IF to check if the cell is blank. Only then it will enter the number in it.
   
4. From here, you need to use the else statement in the IF, and then you need to use the Exit For statement to exit the loop if the condition is met.
   

Sub using_exit_for()
Dim i As Integer
For i = 1 To 10
    If Cells(i, 1) = "" Then
        Cells(i, 1) = i
    Else
        Exit For
    End If
Next i
End Sub

When you run the above code, it will enter the number in the range A1:A10, but if there is a value in any of the cells, it will exit the loop.

Benefits of using the Exit For Statement

Once you know how to use the Exit For statement, it is also important to know some of the benefits that come with it.

1. With Exit For, you can end a loop before its completion (once the condition is met), which helps you save time and increase the speed of the code.
2. It also gives you much control over the code, especially when you want to control its iterations.
3. You can also use the Exit For statement to deal with errors in a code. Or, if you are dealing with data and have doubts about getting an error, you can end the loop with an exit if something like this occurs.