delete row if cell value =

  • Thread starter Thread starter Wanna Learn
  • Start date Start date
W

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
 
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
 
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
 
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.
 
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.
 
Back
Top