macro to delete rows on condition



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,


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, _
If rngFound Is Nothing Then
MsgBox "Sorry. Nothing to Delete"
strFirst = rngFound.Address
Set rngToDelete = rngFound
Set rngToDelete = Union(rngToDelete, rngFound)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
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
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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find next problem 1
rewriting macro to only clear contents 1
help with macro 10
Delete rows macro 3
Resize to last entry in row and copy to sheet 1 3
change code 2
find multiple values code tweak 5
Help with .Find 3