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
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