Delete cell value if

[kashyap]

Hi, I need to delete cell value if the value matches with the list value. something like..

if range("A:A").value=range("C1:C10").value then
activecell.value=clearcontents

can anyone help me out with this pls..

Thanks

 

[VoG]

Try

Sub DelIF()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If IsNumeric(Application.Match(Range("A" & i).Value, Range("C1:C10"), 0)) Then Range("A" & i).ClearContents
Next i
End Sub

 

[kashyap]

works great.. thanks

 

[Weaver]

you could maybe cycle through the list and then use find/replace on the range.

Sub fr2()
    Set rng = Range("A:A")
    For Each c In Range("C1:C10")
        rng.Replace What:=c, Replacement:="", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next c
End Sub