delete row if cell value =

[Wanna Learn]

Hello
I received a daily report , a large spreadsheet ,I need to delete all rows
that have the words SD, MC, MO in column D Thanks in advance
I know how to create a simple delete row
Sub DeleteRows()
Rows("3:" & Rows.Count).Delete

End Sub


I've been using a filter - but a VBA code will be faster thanks in advance

 

[Jim Thomlinson]

This would be my take...

Public Sub MainDelete()
Call DeleteStuff("SD")
Call DeleteStuff("MC")
Call DeleteStuff("MO")
End Sub

Public Sub DeleteStuff(ByVal strToDelete As String)
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set rngToSearch = Columns("D")
Set rngFound = rngToSearch.Find(What:=strToDelete, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Delete
End If
End Sub

 

[Jim Thomlinson]

This would be my take...

Public Sub MainDelete()
Call DeleteStuff("SD")
Call DeleteStuff("MC")
Call DeleteStuff("MO")
End Sub

Public Sub DeleteStuff(ByVal strToDelete As String)
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String
Dim rngToSearch As Range

Set rngToSearch = Columns("D")
Set rngFound = rngToSearch.Find(What:=strToDelete, _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
MatchCase:=True)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
rngFoundAll.EntireRow.Delete
End If
End Sub

 

[Don Guillett]

Use a loop from the BOTTOM up to delete each row if
or
record a macro while filtering on each and deleting.

depending on the number of rows as to the quickest.

 

[Don Guillett]

Use a loop from the BOTTOM up to delete each row if
or
record a macro while filtering on each and deleting.

depending on the number of rows as to the quickest.