Macro loop with messagebox

P

PVT

Hi- can you please help me fix the following macro. It "hangs" when
"no" is chosen at the messagebox. How do I make it go to the next cell
in the "if" statement?

Sub DeleteSearchTerm26()
Dim c As Range

With ActiveSheet.Range("A:A")
Do

Set c = .Find("by", LookIn:=xlValues, lookat:=xlPart, _
MatchCase:=False)

If c Is Nothing Then Exit Do
c.Select

If MsgBox("Delete entire row?", vbYesNo) = vbYes Then
c.EntireRow.Delete Else ActiveCell.Offset
(0,1).Select

Loop
End With

End Sub
 
J

john.mctigue

Hi- can you please help me fix the following macro. It "hangs" when
"no" is chosen at the messagebox. How do I make it go to the next cell
in the "if" statement?

Sub DeleteSearchTerm26()
Dim c As Range

With ActiveSheet.Range("A:A")
    Do

        Set c = .Find("by", LookIn:=xlValues, lookat:=xlPart, _
             MatchCase:=False)

        If c Is Nothing Then Exit Do
        c.Select

        If MsgBox("Delete entire row?", vbYesNo) = vbYes Then
c.EntireRow.Delete Else ActiveCell.Offset
(0,1).Select

             Loop
End With

End Sub

Should that be Inputbox instead of Msgbox? (I've forgotten the little
Excel I knew so that may not be the case.)

Kind regards,
John
 
J

JLGWhiz

In a previous posting I tried to point out that your Do Loop
was not set up properly because it has no defined stopping
point. It will run forever if it does not find the key word.
Below is a macro that I believe will do what you are trying
to do. Give it a try, and post back with any problems.

Sub getBy()
Dim rng As Range, lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A2:A" & lr)
For i = lr To 2 Step -1
If LCase(ActiveSheet.Cells(i, 1).Value) Like "*by*" Then
If MsgBox("DO YOU WANT TO DELETE ENTIRE ROW", _
vbYesNo, "Delete Row?") = vbYes Then
ActiveSheet.Cells(i, 1).EntireRow.Delete
End If
End If
Next
End Sub
 
J

JLGWhiz

In case you need to see what is in the cell before deleting the row, you can
use this modified version. The message box will show what the found cell has
in it.

Sub getBy()
Dim rng As Range, lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A2:A" & lr)
For i = lr To 2 Step -1
If LCase(ActiveSheet.Cells(i, 1).Value) Like "*by*" Then
If MsgBox("DO YOU WANT TO DELETE: " & Cells(i, 1).Value, _
vbYesNo, "Delete Row?") = vbYes Then
ActiveSheet.Cells(i, 1).EntireRow.Delete
End If
End If
Next
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top