macro to delete rows on condition


G

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
 
Ad

Advertisements

P

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
 

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

Top