Find and replace from many columns

[Ottma]

Hello
In sheet1 i have many columns with numbers like this

ColA ColB ColC
123   321  123
111   123  555
444   333  222

In sheet2 i have one column with numbers like this

ColA 
123
222
444
222

I want to search sheet1 for all numbers in sheet2 and remove them from sheet1,
Is that possible, without doing one column at a time?

 

[Takae]

Please try it with sample data.

Sub test()
Dim c
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
With ws1
    For Each c In .Range("A2").CurrentRegion
        If WorksheetFunction.CountIf(ws2.Range("A:A"), c) > 0 Then
            c.ClearContents
        End If
    Next
End With
End Sub

 

[Ottma]

That worked, Thank you
would it possible to get to work if sheet1 looked like this, but it only looked for the numbers in column A to C

ColA ColB ColC ColD
123 123 5467
456 121 1211
321 454 ASC

 

[Ottma]

Sorry, this is what i meant

[COLOR=#333333][I]ColA ColB ColC ColD[/I][/COLOR]
[COLOR=#333333][I]123 123          5467[/I][/COLOR]
[COLOR=#333333][I]456        121 1211[/I][/COLOR]
[COLOR=#333333][I]        321 454 ASC
[/I][/COLOR]

 

[Fluff]

If you numbers are values, rather than the result of formulae, try

Sub removevalues()

   Dim Ary As Variant
   Dim i As Long
   
   Ary = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
   For i = LBound(Ary) To UBound(Ary)
      Sheets("Sheet1").Range("A:C").Replace Ary(i), "", xlWhole, , False, , False, False
   Next i
End Sub