Delete rows that match criteria

I would like a macro that scrolls through each row and deletes it if it contains the word "Account" in column B.

Thanks for any help

There a variety of ways to do this ... some will loop but I quite like to use the following method:

Public Sub RemoveAccount()
With Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)).Offset(0, Columns.Count - 2)
    .FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""Account"",RC2)),1,""x"")"
    .SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Delete
    .Clear
End With
End Sub

To be honest if you wanted it to be dynamic etc you would be best served reverting to the traditional loop & find method ... such that you can easily do partial matches etc...

A slightly more dynamic version of the prior method would be along the lines of:

Public Sub RemoveX()
Dim strSearch As String, rngCol As Range, bErr As Byte
Rem Determine Criteria & Range
strSearch = Application.InputBox("Enter Search String", "Find", Type:=2)
On Error Resume Next
Set rngCol = Application.InputBox("Select Column", "Where", Type:=8)
On Error GoTo 0
Rem Validate
If strSearch = "" Then bErr = 1
If rngCol Is Nothing Then bErr = 2
If bErr = 0 Then bErr = 2 * Abs(rngCol.Columns.Count > 1)
If bErr > 0 Then
    Select Case bErr
        Case 1
            strErrMsg = "Invalid Search String (min 1 Char)"
        Case 2
            strErrMsg = "Invalid Range Selection (max 1 Column)"
    End Select
    MsgBox strErrMsg & vbLf & vbLf & "Routine Terminated", vbCritical, "Fatal Error"
Else
    With Range(Cells(1, rngCol.Column), Cells(Rows.Count, rngCol.Column).End(xlUp)).Offset(0, Columns.Count - rngCol.Column)
        .FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""" & strSearch & """,RC" & rngCol.Column & ")),1,""x"")"
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Delete
        On Error GoTo 0
        .Clear
    End With
End If
Set rngCol = Nothing
End Sub

But to reiterate - making the above more flexible is pretty worthless given standard Find method offers greater inherent flexibility for less effort ;-)

As I say for the odd check here & there I prefer the .SpecialCells method as I'm not a fan of looping... anything more complex... Find & Loop... you should find lots of examples here of the Find & Delete approach.

try this if this is of any help


Below code will ask you what you want to delete and in which column u want to search

Eg. Input what u want to delete as "Account"
and for Column enter 1 , 2 , 3 etc.

Sub del_macro()
    Dim key As String
    Dim col As Integer
    
    
    key = InputBox("Enter the string to search and delete")
    col = InputBox("Enter the coloum to search the string for deleting")
    
    Dim rTable As Range
    Cells(1, 1).Select
    Set rTable = Selection.CurrentRegion
    Selection.AutoFilter field:=col, Criteria1:=key
    
    rTable.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.AutoFilterMode = False
    last_row = ActiveSheet.UsedRange.Rows.Count

    
End Sub