Removed Named range rows based on cellvalue in multiple column using VBA-excel

[achu]

Dear Excel Experts ,

Can you please someone help me creating VBA code to removing Charachter containes in columns using Name range ,

Explain : - i have a data set name Column A: - Plant code , Column B:- Building details, in those two columns few rows have character containing as below given , i want to remove entire rows where this character shown,

created name manager : = removed

in excel formula =index(removed,match(true, isnumber(search(removed,a2)),0) , i am trying to convert this excel formula to vba code. please help this

GLEN

CANC

Resale

Scrap

Transfer

Unsold

#

COAST_GUARD

NOT TO USE

SUBLEASE

UNIDENTIFIED

LEASE

NOT DECIDED

 

[p45cal]

try:

Sub blah()
Dim CellsToDelete As Range

remVals = Range("removed").Value
With Range("A2:B3214")    'these are the cells you're examining
  Vals = .Value
  For rw = 1 To UBound(Vals)
    FoundInRow = False
    For Colm = 1 To UBound(Vals, 2)
      For j = 1 To UBound(remVals)
        If InStr(1, Vals(rw, Colm), remVals(j, 1), vbTextCompare) > 0 Then
          If CellsToDelete Is Nothing Then
            Set CellsToDelete = .Cells(rw, Colm)
          Else
            Set CellsToDelete = Union(CellsToDelete, .Cells(rw, Colm))
          End If
          FoundInRow = True
          Exit For
        End If
      Next j
      If FoundInRow Then Exit For
    Next Colm
  Next rw
End With
If Not CellsToDelete Is Nothing Then CellsToDelete.EntireRow.Delete    'Select
End Sub

 

[achu]

Dear Sir,
;0
Thank you for this help, this formula working good , you are a awesome