Delete Row based on cell content

[deduwa]

Hi,

Is it possible to code something that does the following in the current sheet (Sheet1).

(I have 2 tabs which are Sheet1, AA)

1. If cell value in F1 of "AA" tab = 1 then in Sheet1 find the term ABC and delete the entire row that contains this ABC term

2. If cell value in F1 of "AA" tab = 2 then in Sheet1 find the term UVW and XYZ and delete the two rows entirely that contain these two terms

Hope I have made it clear.

Thanks

 

[rpaulson]

untested,

try this on a copy of your file:

Sub DeleteRows()
Application.ScreenUpdating = False

If Worksheets("AA").Range("F1") = 1 Then
    With Worksheets("Sheet1").Cells
        .Replace "ABC", True, xlWhole ' can also use *John* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
End If


If Worksheets("AA").Range("F1") = 2 Then
    With Worksheets("Sheet1").Cells
        .Replace "UVW", True, xlWhole ' can also use *John* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
    
    With Worksheets("Sheet1").Cells
        .Replace "XYZ", True, xlWhole ' can also use *John* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
End If

Application.ScreenUpdating = True
End Sub

hth,

Ross