title: Excel clear cells based on contents of a list in another sheet
body: <p>I have an excel Sheet1 of a thousand of rows and 20 columns from A1 to T1. Each cell in that range has some data in it, usually one or two words.
  In Sheet2, A1 column I have a list of data of 1000 values.</p>
  
  <p>I am working on VBA script to find words from Sheet2 list in Sheet1 and clear the values of the cells of the found ones.</p>
  
  <p>I now have a VBA script that works only on A1 column of Sheet1 and it deletes the rows only. Here's the script:</p>
  
  <pre><code>Sub DeleteEmails() 
  Dim rList As Range 
  Dim rCrit As Range 
  
  With Worksheets("Sheet1") 
      .Range("A1").Insert shift:=xlDown: .Range("A1").Value = "Temp Header" 
      Set rList = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)) 
  End With 
  With Worksheets("Sheet2") 
      .Range("A1").Insert shift:=xlDown: .Range("A1").Value = "Temp Header" 
      Set rCrit = .Range("A1", .Cells(Rows.Count, 1).End(xlUp)) 
  End With 
  
  rList.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False 
  rList.Offset(1).SpecialCells(xlCellTypeVisible).Delete shift:=xlUp 
  Worksheets("Sheet1").ShowAllData 
  
  rList(1).Delete shift:=xlUp: rCrit(1).Delete shift:=xlUp 
  
  Set rList = Nothing: Set rCrit = Nothing 
  End Sub 
  </code></pre>
  
  <p>Could anyone help me? I need the values cleared, not rows deleted, and this should work on all columns of Sheet1, not just A1.</p>