Macro loop with messagebox
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" wrote:
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
|