Excel VBA vbYesNo MsgBox

Question

Can I get some help with the syntax of the vbYesNo MsgBox? I have this right now,

For i = 1 To 10
    If Not (cells(i,1).value = "0" OR cells(i,1).value = "30") Then
        Range(cells(i, 1), cells(i, 1).End(xlToRight)).Select
        ans = MsgBox("Do you want to delete this row?", vbYesNo)
        If ans = vbYes Then
            'I do something
        Else
            'I want to exit this the If and continue on the For loop
            Exit If
        End If
    End If
Next i

I tried End If and Exit If but doesn't work. Can I get some help?

It would help to see where and how you're assigning ans......... — Mathieu Guindon
Why not just do End IF? You don't always have to put Else with If statements. Note though, your loop will not prompt the user 10 times for an answer. They're asked once (before the loop) and based on that, the loop will either do things, or skip completely. — BruceWayne
Oh wow, your question has completely nothing to do with vbYesNo/MsgBox. I suggest you take your actual, working code over to Code Review... it looks like something is terribly inefficient in what you're doing here. e.g. why loop even once when ans isn't vbYes? — Mathieu Guindon
@Mat's Mug: Sorry if my title wasn't clear. This is a simple version of what I have, I have a nested If condition inside my loop, so even when its vbNo I still need to go through the whole loop. — Bubs

Kyle Kyle · Accepted Answer · 2016-06-17T20:48:32

For i = 1 To 10
    If ans = vbYes Then
        'Do whatever
    End If
Next i

If you leave out the Else, it just resumes next if the If clause isn't met.

EDIT: When you delete rows in a loop, you should ALWAYS loop in reverse. I made a couple other tweaks as well. See below:

For i = 10 To 1 Step -1
    If Not (cells(i,1).value = "0" OR cells(i,1).value = "30") Then
        ans = MsgBox("Do you want to delete row " & Left(cells(i,1).Address(False,False),1) & "?", vbYesNo)
        If ans = vbYes Then
            Cells(i,1).EntireRow.Delete
        End If
    End If
Next i

Excel VBA vbYesNo MsgBox

2 Answers