Delete Entire row if cell does NOT match?

[rpaulson]

hello,

the code below is, by far, the easiest and fastest I have ever seen to delete an entire row.

Sub DeleteRows() 'This macro will delete the entire row if there is a data match anywhere in the range.
   
    With Range("A1:A:500")
        .Replace "John", True, xlWhole ' can also use *John* if searching for any part
        .SpecialCells(xlCellTypeConstants, 4).EntireRow.Delete
    End With
End Sub

what I would like to know is how to tweak the about code to delete the entire row if it does NOT mach the criteria?

example: the above will delete the row if "John" is in Column A
I would like to delete the row if "John" is NOT in Column A

thanks for looking,

Ross

 

[Fluff]

How about

Sub DeleteRows() 'This macro will delete the entire row if there is a data match anywhere in the range.
  
    With Range("A2:A500")
        .Replace "John", "=xxxJohn", xlWhole ' can also use *John* if searching for any part
        .SpecialCells(xlCellTypeConstants).EntireRow.Delete
        .Replace "=xxxJohn", "John", xlWhole
    End With
End Sub

 

[rpaulson]

Fluff,

awesome: very nice
i set the string as variable. (see revised code)

there is an issue that arises:
if there is match (no rows to delete)
the code changes the cell value and display up an error.
and idea on how to fix that?

Sub KeepRows()
 Dim keep As String
  keep = "John"
  
    With Range("A2:A500")
        .Replace keep, "=xxx" & keep, xlWhole
        .SpecialCells(xlCellTypeConstants).EntireRow.Delete
        .Replace "=xxx" & keep, keep, xlWhole
    End With
End Sub

Thanks,
Ross

 

[Fluff]

You can wrap it in a On Error

Sub KeepRows()
 Dim keep As String
  keep = "John"
  
    With Range("A2:A500")
        .Replace keep, "=xxx" & keep, xlWhole
        On Error Resume Next
        .SpecialCells(xlCellTypeConstants).EntireRow.Delete
        On Error GoTo 0
        .Replace "=xxx" & keep, keep, xlWhole
    End With
End Sub

 

[rpaulson]

txs

 

[Fluff]

You're welcome.

 

[jolivanes]

Would this be a valid alternative?

Sub Delete_Non_John_by_Fluff() 'This macro will delete the entire row if there is a data match anywhere in the range.
If WorksheetFunction.CountIf(Columns(1), "John") = 0 Then MsgBox "No Johns!": Exit Sub
Application.ScreenUpdating = False
    With Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        .Replace "John", "=xxxJohn", xlWhole ' can also use *John* if searching for any part
        .SpecialCells(xlCellTypeConstants).EntireRow.Delete
        .Replace "=xxxJohn", "John", xlWhole
    End With
Application.ScreenUpdating = True
End Sub