Search and replace

[aerosmith]

HI there, would there be an easy way to search a worksheet for 47 specific words within the entire sheet and replace it with null or blank,

 

[ssianh]

Use the search and replace function ?
it's relatively easy by clicking replace all

 

[Rick Rothstein]

Give this macro a try where I have assumed the words you want to replace fill up the cells they are in (that is, no other text is in the cell with them)...

Sub DeleteWords()
  Dim Word As Variant
  Const WordList = "One,Two,Three,Four,Five,etc."
  For Each Word In Split(WordList, ",")
    Cells.Replace Trim(Word), "", xlWhole
  Next
End Sub

Simply replace my sample list of words in the WordList constant (the Const statement) with your own words.

 

[RSXchin]

so THAT'S how you use Split? that's awesome. Def be using that in the future.

 

[Rick Rothstein]

so THAT'S how you use Split? that's awesome. Def be using that in the future.

The more common usage of Split is to create an array and assign it to a dynamic array variable that can be used later on in your program. For example...

Dim X As Long, MyArray() As String
....
....
MyArray = Split("One,Two,Three,Four", ",")
....
....
' Let's see what's in MyArray
For X = 0 To UBound(MyArray)
  MsgBox MyArray(X)
Next

Note that Split always returns a zero-based array no matter what Option Base setting you might be using. I just mentioned that in case you were wondering why I used 0 instead of LBound(MyArray) in the For statement.

 

[aerosmith]

Give this macro a try where I have assumed the words you want to replace fill up the cells they are in (that is, no other text is in the cell with them)...

Sub DeleteWords()
  Dim Word As Variant
  Const WordList = "One,Two,Three,Four,Five,etc."
  For Each Word In Split(WordList, ",")
    Cells.Replace Trim(Word), "", xlWhole
  Next
End Sub

Simply replace my sample list of words in the WordList constant (the Const statement) with your own words.

Sorry for the delay,

This is amazing, Works Exactly the way i need it, I undertand why people asked why i didint use the normal replace method, i have approx 47 items to run the script against. on a daily basis, and this works excellent.