Checking cells for one of multiple data

I want to make a Script that checks the rows, wen the second all the rows on the first column are done, check the next one, and so on.

I want to remove some words from an excel table, the problem is that there are a lot of words.

I'd like to do something like this: IF A1 = car OR boat OR train ... for each cell that has text in it. if the cell contains the specified text, to remove it.

Can anyone give so开发者_开发知识库me examples?

thanks, Sebastian

Try using Find/Replace through VBA. It is very fast.

Sub SearchAndDestroy()
    Dim SearchWordCell As Range

    For Each SearchWordCell In Range("A1:A50") 'Asuming that range A1:A50 is the list with the 50 words you want to search/replace

        Range("C10:R4510").Replace What:=SearchWordCell.Value, Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False  'Asuming that range C10:R4510 is the table where you want to find and delete the words.

    Next SearchWordCell

End Sub

Just modify as needed.

Store the list of words that you want to delete in a range and cycle through this range.

Example:

Sub DeleteFromWordList()
Dim InRange As Range, CritRange As Range
Dim InCell As Range, CritCell As Range

    Set InRange = Selection                  ' all selected source cells
    Set CritRange = Range("Words2Delete")    ' the named range of words to be excluded

    For Each InCell In InRange.Cells
        For Each CritCell In CritRange.Cells
            If InCell = CritCell Then
                InCell = ""                  ' blank it
                Exit For                     ' exit inner for
            End If
        Next CritCell
    Next InCell
End Sub

Hope that helps .... good luck MikeD