CODEWITHSUNDEEP
excelvbaautomation
SickAndTired
SickAndTired

Reputation: 27

Check if value exists in row, then delete

I have always to delete thirty values from a column, I have managed to "create" a code, that does that, however when one of the values is not found, the code bugs and deletes the entire column. I'm looking for some piece of code that ckecks if the values exists, if not it just skips it.

I'd rather not use that kinda code that prompts a text box to input the value, since I have to delete 30 of them, which have different names.

While writing this question I tried this so far, however the codes keeps asking for the next object, which requires me to click at lest 29 times

Sub IfContains()
    If InStr(ActiveCell.Value, "string1") > 0 Then
        ActiveCell.Delete = True
    Else
        If InStr(ActiveCell.Value, "string2") > 0 Then
            ActiveCellDelete = True
        End If
    End If
End Sub

Upvotes: 0

Views: 703

Answers (1)

Gove
Gove

Reputation: 1814

This might be a better approach. You have only one string to configure, delimited by the tilde (~) character. Then split that into an array of individual values and use a loop to look for every value of the array.

Sub IfContains()
    Dim x As Integer
    Dim values As Variant
    values = "string1~string2~string3" ' using a tilde (~) to separate the values
    values = Split(values, "~") ' make an array of individual values
    
    For x = 0 To UBound(values)
        If InStr(1, ActiveCell.Value, values(x)) > 0 Then ActiveCell.Delete
    Next

End Sub

Upvotes: 1

Related Questions