CODEWITHSUNDEEP
vbaexcel
Andrew
Andrew

Reputation: 13

VBA code for finding a partial string match

I'm looking to use this code but modified so that it finds any cells that contain the words Abuse Neglect (like a partial match) - and then subsequently deletes the row. Any help is appreciated!

Sub TakeOutAllOtherCourses()
Last = Cells(Rows.Count, "D").End(xlUp).Row
For i = Last To 1 Step -1
    If (Cells(i, "D").Value) = "Abuse Neglect" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
        Cells(i, "A").EntireRow.Delete
    End If
Next i

Upvotes: 0

Views: 13017

Answers (3)

DisplayName
DisplayName

Reputation: 13386

Should your column D have no blanks, here’s a possibile (but untested) way with neither AutoFilter nor loops:

Sub TakeOutAllOtherCourses()
    With Range("D1", Cells(Rows.Count, "D").End(xlUp))
        .Replace What:="Abuse Neglect*", Replacement:="", LookAt:=xlPart
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Upvotes: 0

warner_sc
warner_sc

Reputation: 848

Check how Like operator works in this link (Like Operator - Visual Basic)

Code:

Sub TakeOutAllOtherCourses()
Last = Cells(Rows.Count, "D").End(xlUp).Row
For i = Last To 1 Step -1
    If (Cells(i, "D").Value) Like "Abuse Neglect*" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
        Cells(i, "A").EntireRow.Delete
    End If
Next i
End Sub

Upvotes: 1

YasserKhalil
YasserKhalil

Reputation: 9568

Try Instr

Sub TakeOutAllOtherCourses()
last = Cells(Rows.Count, "D").End(xlUp).Row
For i = last To 1 Step -1
    If InStr(Cells(i, "D").Value, "Abuse Neglect") > 0 Then
        Cells(i, "A").EntireRow.Delete
    End If
Next i
End Sub

Upvotes: 2

Related Questions