EXCEL Remove multiple values from multiple cells 1

[ITALIAORIANA]

Hi,

Is there a way to remove a list of items that are in multiple cells? I have a sample of the list below (there are a total of 687 codes). I am looking to find each one of those codes anywhere on the sheet and remove them without having to do each one manually.


'0CGG','6DEI','9CHO','A040','62SP','6THO','A020','AN32','62NI','6RPO','A010','ATX','6RNI','A027','AUO','CDCY','DMO2','IAIY','NSO',
'CLTR'


I found the code below online that looked great but I keep getting a compile error on the "For i = 1" section that says can't find project or library. Any help would be greatly appreciated.

Thanks
Deana

Sub abbrev()
Dim abvtab() As Variant
Dim ltsheet As Worksheet
Dim datasheet As Worksheet
Dim lt As Range

'Change Lookup to the sheet name with your lookup table.
Set ltsheet = Sheets("Lookup")

'Change Data to the sheet name with your data.
Set datasheet = Sheets("Data")

'Change A1 to the top left cell (not the header) in your lookup table.
'Change B1 to top right cell.
Set lt = ltsheet.Range("A1", ltsheet.Range("B1").End(xlDown))

abvtab = lt

For i = 1 To UBound(abvtab)
datasheet.Cells.Replace What:=abvtab(i, 1), Replacement:=abvtab(i, 2), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

 

[Andrzejek]

Since this is a VBA question - you may have a better chance of getting the answer from forum707

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[zelgar]

I think with the minimal changes in code, your macro will work:

Sub abbrev()
 Dim abvtab() As Variant
 Dim ltsheet As Worksheet
 Dim datasheet As Worksheet
 Dim lt As Range

 'Change Lookup to the sheet name with your lookup table.
 Set ltsheet = Sheets("Lookup")

 'Change Data to the sheet name with your data.
 Set datasheet = Sheets("Data")

 'Change A1 to the top left cell (not the header) in your lookup table.
 'Change B1 to top right cell.
 Set lt = ltsheet.Range("A1", ltsheet.Range("B1").End(xlDown))

[s] abvtab = lt

  For i = 1 To UBound(abvtab)
 datasheet.Cells.Replace What:=abvtab(i, 1), Replacement:=abvtab(i, 2), LookAt:=xlPart, _
 SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
 ReplaceFormat:=False[/s]
[b]For i = 1 to lt
    datasheet.Cells.Replace What:=ltsheet(Range(Cells(i, 1)), Replacement:=ltsheet(Range(Cells(i, 2)), LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
    ReplaceFormat:=False [/b]
 Next i
 End Sub

 

[ITALIAORIANA]

AMAZING!!! Thanks so much zelgar.