macro to delete rows on condition

[Guest]

Hi, I have this macro below (which works great and I found here) which
deletes a row upon a condition and I would like it to do something different.
Right now it deletes a row if a cell is found that contains something
particular. I want to reverse that and delete every row that does NOT have
the particular term. How do I do that?

so when the cell column D does NOT say "COST CODE TOTAL" it would be deleted.


Thanks so much,


Todd


Sub DeleteRowsOnCondition()
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirst As String
Dim rngToDelete As Range

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("d")
Set rngFound = rngToSearch.Find(What:="COST CODE TOTAL")
' Set rngFound = rngToSearch.Find(What:="COST CODE TOTAL", _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry. Nothing to Delete"
Else
strFirst = rngFound.Address
Set rngToDelete = rngFound
Do
Set rngToDelete = Union(rngToDelete, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngToDelete.EntireRow.Delete
End If

End Sub

 

[Paul B]

Todd, here is some code by Chip Pearson, changed to do what you want


Sub test()
'will delete a rows that DO NOT have COST CODE TOTAL in column D
Dim LastRow As Long
Dim RowNdx As Long
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "D") <> "COST CODE TOTAL" Then 'your text here
Rows(RowNdx).Delete
End If
Next RowNdx

End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003