0
votes

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?

2
Have your tried Exit For instead of Exit If? - VBA Pete
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

2 Answers

2
votes
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
0
votes

To delete the row I suggest creating a variable then using the range's row and delete it. At the start add Dim myRng as Range then replace your .Select line with

Set myRng = Range(cells(i, 1), cells(i, 1).End(xlToRight))

Then in your "if yes",

myRng.EntireRow.Delete

Then remove the Else altogether.

Note: I think your range should work but I'm not sure about the second cells(), so let me know if it's giving you issues.