Deleting rows containing certain values

[walrasianxl]

Hi all,

I want to delete rows containing certain values: For example, this is a rough picture of what i have:

Name1 Name2 Value
Amy Eric 24
Ted Chris 18
Jon Liza 20
Amy Eric 11
Amy Annie 15

I would like to delete "Amy" from the column "Name1". How do i go about doing it?

Many thanks!

[Marcol]

Try this in a standard module

Option Explicit

Sub test()
    DeleteRowsContaining "Amy"
End Sub

Sub DeleteRowsContaining(strFind As String)

    On Error Resume Next

    Do
        Cells.Find(What:=strFind, After:=ActiveCell, LookIn:=xlFormulas, _
                   LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False).EntireRow.Delete
        If Err.Number <> 0 Then
            Err.Clear
            On Error GoTo 0
            Exit Do
        End If
    Loop
End Sub

Hope this helps

[EDIT]
If you just want to delete "Amy" and not the whole row use Replace > Find What:= "Amy" > Replace With:= Leave this field blank

[Palmetto]

Another option using filtering, which is faster than looping.
User is prompted via an input box to enter the name, which is passed to the filter as the critiera.

Note: this code uses the sheet code name -- not the worksheet name. You can replace "Sheet1" with "ActiveSheet" to make the code a bit more generic.

Option Explicit

Sub Delete_Names()

    Dim sCriteria As String, rng As Range, lastrow As Long
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    sCriteria = Application.InputBox("Enter a name: ", Type:=2)
    
    If Len(sCriteria) < 1 Then Exit Sub
    
    sCriteria = "*" & sCriteria & "*"
    
    Application.ScreenUpdating = False
    
    With Sheet1 'code name - - not worksheet name
    
        .AutoFilterMode = False
        Set rng = .Range("A1:A" & lastrow)
        rng.AutoFilter Field:=1, Criteria1:="=" & sCriteria
        rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
        
    End With
    Set rng = Nothing
    Application.ScreenUpdating = True

End Sub