Find and delete depending on answer to msgbox then continue loop

[Dkeller12]

Hello,

I am trying to build a macro that will search a column for the word "TEST" and when it finds that word it needs to prompt the user with a YesNo message box (or something similar) if they would like to delete the entire row that contains TEST. If Yes is selected, it should delete the row and continue on to find the next TEST in the column and repeat this process. If the user selects No, it should continue on to find the next TEST in the column and repeat this process.

Can anyone help me out. Thanks!

 

[JLGWhiz]

If you are going to delete rows, then start the loop from the bottom of the file so that when the row is deleted it does not corrupt the sequence of the loop. The code below assumes column A as the search range where "TEST" would be found.


Code:
Sub delTest()
Dim sh As Worksheet, rng As Range, lr As Long, choose As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row 'Fing last row number in range
Set rng = Range("A2:A" & lr) 'Establish search range
For i = lr To 2 Step - 1 'Establish search order
If UCase(sh.Cells(i, 1)) = "TEST" Then
choose = MsgBox("DO YOU WANT TO DELETE ROW " & i & "?", vbYesNo, "DELETE OPTION")
If choose = vbYes Then
Rows(i).Delete
End If
End If
Next
End Sub
Code:

 

[Dkeller12]

Thank you for your response. I apologize for not responding sooner, vacation got in the way.

This works great and is exactly what I wanted however I have a followup question. Can this be adjusted to catch cells that not only equal "TEST" but that also contain "TEST" with other words in the same cell and not just "TEST" alone? For example: say a cell says "TEST DATA" or "TEST INFO" and not just "TEST". Right now it is only finding "TEST" but I have found others that have "TEST" with random words included in the cell that are not being deleted since the it does not equal "TEST" only.

Again, thanks for the help!

 

[JLGWhiz]

Thank you for your response. I apologize for not responding sooner, vacation got in the way.

This works great and is exactly what I wanted however I have a followup question. Can this be adjusted to catch cells that not only equal "TEST" but that also contain "TEST" with other words in the same cell and not just "TEST" alone? For example: say a cell says "TEST DATA" or "TEST INFO" and not just "TEST". Right now it is only finding "TEST" but I have found others that have "TEST" with random words included in the cell that are not being deleted since the it does not equal "TEST" only.

Again, thanks for the help!

I thought I had answered this before, but with all the down time and rework of the system, I guess it got lost. Yes, this code will do what you want.

Code:
Sub delTest()
Dim sh As Worksheet, rng As Range, lr As Long, choose As Variant
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row 'Fing last row number in range
Set rng = Range("A2:A" & lr) 'Establish search range
For i = lr To 2 Step -1 'Establish search order
If InStr(1, UCase(sh.Cells(i, 1)), "TEST") > 0 Then
choose = MsgBox("DO YOU WANT TO DELETE ROW " & i & "?", vbYesNo, "DELETE OPTION")
If choose = vbYes Then
Rows(i).Delete
End If
End If
Next
End Sub
Code:

 

[Dkeller12]

This is perfect! Thank you for taking the time to help me out with this.