Delete Row If Cell Does Not Contain "text"

[Cue]

I'm tring to delete a row that has a cell that contains words but does not
contain a specific "text".

Ex. Cell A2 = Jill - Good

I want to delete Row 2 becasue Cell A2 does not contain "Bad". The Macro is
following:

Sub Delete Row()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If Cells(r, 3).Value <> "text" Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Can somebody show me what is missing or needed to accomplish this?

 

[Mike H]

Maybe this approach

Sub delete_It()
Dim MyRange1 As Range
Dim MyRange As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If InStr(1, UCase(c.Value), "BAD", 0) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If

End Sub

Mike

 

[Cue]

I tried your suggestion. It deleted rows with "BAD" in the cell. I want it to
delete those cells that don't have "BAD" in the cell. Do you have another
suggestion?

 

[Mike H]

I misread your post,

substitute this line in the macro

If InStr(1, UCase(c.Value), "BAD", 0) = False Then

Mike

 

[Cue]

Perfecto!

Thanks Mike!
--
Cue

I misread your post,

substitute this line in the macro

If InStr(1, UCase(c.Value), "BAD", 0) = False Then

Mike

 

[Mike H]

your welcome and thanks for the feedback. I trust my misread didn't trash too
much data


Mike

 

[Gord Dibben]

Sub Delete_Row()
'looks only at Column A
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If Cells(r, 1).Value <> "Bad" Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


Gord Dibben MS Excel MVP