How to delete all rows that don't have a specific name - e.g. "XYZ"

[truzilla]

Hello All!

Thanks in advance for your time and effort in helping me with this problem.

So I need code that will basically delete all the rows that dont have a specific word in that row. For example, lets say rows 1-10 have a list of people. I want to delete all the rows that dont have John and Sally. The problem is John and Sally show up in different rows everytime so we can't just choose specific cells. Thanks and I hope thats clear! : pray2:

[Bob Phillips]

Sub DeleteRows()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Evaluate("SUMPRODUCT(COUNTIF(" & i & ":" & i & ",{""John"",""Sally""}))") = 0 Then

.Rows(i).Delete
End If
Next i
End With
End Sub

[truzilla]

hey, thanks for the quick response! but I actually have a problem. I actually need it to delete a couple values (names from the example). So if I run that once, it will delete everything that is not XYZ but I actually want XYZ and ABC.

Also, can I just make it search the range B2 and down instead of the whole B column? Thanks!

[Bob Phillips]

Re-check my post, I correc ted it.

[truzilla]

thanks XLD, that works well, the only problem is...how do I tweak it to search for more than 2 words/people. I actually need to search for 6 words and delete the rest that aren't those. Sorry about the constant updates!! I really appreciate your help.

[Bob Phillips]

I have showed you two words, just extend that array

[truzilla]

would it look like this?


Sub DeleteRows()
Dim LastRow As Long
Dim i As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If .Evaluate("SUMPRODUCT(COUNTIF(" & i & ":" & i & ",{""John"",""Sally"",""Tim""}))") = 0 Then

.Rows(i).Delete
End If
Next i
End With
End Sub


or is there more to add?

[Bob Phillips]

Yes.

[truzilla]

Yes, that works marvelously. I am truly amazed by your help and genius!!! =)