How do I delete all rows containing a certain text string? / rows containg x, y. or z

Hi

Try this

Sub deleteRows()
    Dim rng    As Range
    Dim rng2   As Range
    Dim cl     As Range
    Dim str    As String

    'str = InputBox("Enter search text", "Find & Delete")
    'If str = "" Then Exit Sub
    Set rng = ActiveSheet.UsedRange
    For Each cl In rng
        If InStr(1, cl.Value, "fred") > 0 Or InStr(1, cl.Value, "paul") > 0 Or InStr(1, cl.Value, "jim") > 0 Then
            If rng2 Is Nothing Then
                Set rng2 = cl
            Else: Set rng2 = Union(rng2, cl)
            End If
        End If
    Next cl
    rng2.EntireRow.Delete
  End Sub

rylo

hey many thanks --- is there a tweak though so that for example "Paul" row would be deleted but it would not also delete "Paula" and "Apauling" -- but this gets me most of the way ----

Hi

If the words are surrounded by spaces, then include the spaces in the string. So for jim it would be

instr(1,cl.value, " jim ")

If it isn't likely to be contained within spaces, then what combinations would you be likely to expect? " jim" or "jim " perhaps?

rylo

gets that's basic --- thanks ---

Just posted what I think will be my last part of the project I am working through

"assistance in changing data in one column based on another" if you got the time and inclination --- Phillip