• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

achu

Member
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
 

Attachments

p45cal

Well-Known Member
try:
Code:
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
 
Top