0

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?

Bubs
  • 85
  • 1
  • 1
  • 11
  • Have your tried Exit For instead of Exit If? – VBA Pete Jun 17 '16 at 20:47
  • It would help to see where and how you're assigning `ans`......... – Mathieu Guindon Jun 17 '16 at 20:48
  • 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 Jun 17 '16 at 20:48
  • 4
    Oh wow, your question has **completely nothing to do with vbYesNo/MsgBox**. I suggest you take your *actual, working code* over to [codereview.se]... 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 Jun 17 '16 at 20:50
  • @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 Jun 17 '16 at 21:13
  • @BruceWayne: I tried End If and it said I had an End If but not If to end it. – Bubs Jun 17 '16 at 21:13
  • @VBA Pete: I need to stay in the For loop, but I tried Exit if and I get an error – Bubs Jun 17 '16 at 21:14
  • As @Mat'sMug pointed out - your issue is in code you haven't posted. Read your code. If the user clicks No, it's **never** going to do your `I do something` code. Can you include the other code surrounding this part? – BruceWayne Jun 17 '16 at 21:16
  • @BruceWayne I added the other parts of the code – Bubs Jun 17 '16 at 22:08

2 Answers2

2
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
Kyle
  • 2,543
  • 2
  • 16
  • 31
  • Thanks Kyle, is there a particular reason why I should loop in reverse? I have my msgbox and loop working now, but there seems to be another problem. Every time I delete a row, I have it shifted up so there won't be a row of empty cells. The loop checks an additional row at the end eventhough I did something like i=i-1 – Bubs Jun 21 '16 at 19:26
  • You always loop in reverse because when you delete a row, all other rows shift up one. So, if you delete row 2, row 3 becomes row 2, but your loop will go to 3, meaning you never checked and possible deleted what was row 3 in your original sheet. – Kyle Jun 22 '16 at 13:47
0

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.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110