Delete entire row (user defined range and text via inputbox)

[Dustinkli]

Good day,


I've searched high and low to try to find a working VBA for this but I can't seem to find one that meets my needs.


I am trying to write a VBA code or find a VBA code that enables me to select a range of cells and then to specify an input so that it searches that word in the cells and deletes the entire row if it's in the cell. I'd like it to be able to do it based on if the cell contains just that word but also if the cell contains that plus other words. For instance if I put in a "*" before and after the word it'll delete every row if a cell in the specified column has that word anywhere in the cell.


I have this VBA which lets me search "SpecificWord" and deletes all rows that have that in column C, but I'd love to be able to add an input box to it to specify the range and the input value of what to delete. In the below example it searches all cells that have that term anywhere in it. If I remove the "*" at the beginning and end it'll search only if the entire cell is that value.

----------------------------
Sub delete if cell contains()


With ActiveSheet
.AutoFilterMode = False
With Range("C1", Range("C" & Rows.Count).End(xlUp))
.AutoFilter 1, "*SpecificWord*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With


End Sub
----------------------------

 

[Yongle]

Wildcard removed from code as requested

To test
- click on ONE cell in the column to be filtered before running the macro
(assumes data headers are in row 1)

Sub delete_if_cell_contains()
    Dim c As Long, r As Long
    With ActiveSheet
        c = ActiveCell.Column
        r = .Cells(Rows.Count, c).End(xlUp).Row
        .AutoFilterMode = False
        With .Cells(1, c).Resize(r)
            .AutoFilter 1, InputBox("Search for?")
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub

 

[Dustinkli]

Good day,

Yongle: Thanks. It works great!