Macro loop with messagebox

[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

 

[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

 

[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

 

[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

 

[PVT]

That was the intention- and it worked perfectly. Thank you for your
help!