Excel VBA - exit for loop

Question

I would like to exit my for loop when a condition inside is met. How could I exit my for loop when the if condition has been met? I think some kind of exit at the end of my if statement, but don't know how that would work.

Dim i As Long
For i = 1 To 50
    Range("B" & i).Select
    If Range("B" & i).Value = "Artikel" Then
        Dim temp As Long
        temp = i
    End If
Next i
Range("A1:Z" & temp - 1).EntireRow.Delete Shift:=xlToLeft

`If [condition] Then Exit For` inside your loop – Dan Feb 23 '12 at 14:31 — Dan, Feb 23 '12 at 14:31

score 370 · Accepted Answer · answered Feb 23 '12 at 14:39

370

To exit your loop early you can use Exit For

If [condition] Then Exit For

answered Feb 23 '12 at 14:39

Dan

5,081
1
18
28

3

@nixda Please remove your comment, as the hyperlink you shared points to VB.NET documentation, not Office VBA documentation. VBA's `Exit` statement has fewer options than VB.NET's. In fact, VBA only supports: `Exit Do` `Exit For` `Exit Function` `Exit Property` and `Exit Sub`. VBA has no `Exit While`. The correct link is: [Office VBA Reference -Exit statement](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/exit-statement) – Excel Hero Mar 26 '20 at 18:21
@ExcelHero Done – nixda Mar 27 '20 at 15:01

score 29 · Answer 2 · answered Oct 10 '15 at 23:17

29

Another way to exit a For loop early is by changing the loop counter:

For i = 1 To 10
    If i = 5 Then i = 10
Next i

Debug.Print i   '11

For i = 1 To 10
    If i = 5 Then Exit For
Next i

Debug.Print i   '5

answered Oct 10 '15 at 23:17

paul bica

10,557
4
23
42

4

Assign a loop counter is much less robust and future proof than explicitly exiting the loop. The former can be broken by a change in the loop counter's maximum as the code is modified if the editor doesn't notice it's also being used elsewhere. It also doesn't apply to `For Each` loops. – jpmc26 Feb 23 '18 at 22:09
3

This is simply another solution to the question specific to the `For` loop (not `For Each`). Pro’s and con’s apply to different solutions and different situations - for example this is a more robust format than the `Go To label` which brakes the logic (non structured programming), or a possible workaround of the `Continue` keyword, missing in VBA. The merits of `Exit For` can be questioned as well if the logic doesn't perform proper cleanup of objects - bad implementations are not quite relevant here though (@jpmc26) – paul bica Feb 23 '18 at 23:09

score 1 · Answer 3 · answered Feb 29 '20 at 10:59

The first answer given with the following is indeed i.m.o. best practice:

if i = 0 then exit for

However, this is also an option:

Sub some()

Count = 0
End_ = ThisWorkbook.Sheets(1).Range("B1047854").End(xlUp).Row

While Count < End_ And Not ThisWorkbook.Sheets(1).Range("B" & Count).Value = "Artikel"
    Count = Count + 1
    If ThisWorkbook.Sheets(1).Range("B" & Count).Value = "Artikel" Then
        ThisWorkbook.Sheets(1).Range("A1:Z" & Count - 1).EntireRow.Delete Shift:=xlToLeft
    End If
Wend

End Sub

score 0 · Answer 4 · answered Apr 19 '22 at 05:45

0

if condition (inside your while - loop) then goto (some marker outside the loop)

answered Apr 19 '22 at 05:45

user18858896

1

1

It's a possible solution and probably not the best practice. To be avoided if not necessary in your particular circumstances. – Henrik K Apr 19 '22 at 09:01

Excel VBA - exit for loop

4 Answers4

Linked