Find and Replace function in larger mode

[HelpExc]

Can I use this code for 400 words what is needed to replace with spaces?
If a copy all those 400 words into "What = Array("")" line then all those 400 word become red(maybe it becomes red because all those 400 words in going in 9 rows)

What code can i use to replace 400 diffrents word with spaces?


[Select Code] copy to clipboard

Sub ExampleA()
Dim Rng As Range
Dim What As Variant
Dim Repl As Variant
Dim i As Long


What = Array("XYZ000001", "XYZ000002", "XYZ000004")
Repl = Array("ABC000123", "ABC000124", "ABC000125")

For i = 0 To UBound(What)

Set Rng = ActiveSheet.UsedRange
Rng.Replace What:=What(i), Replacement:=Repl(i), MatchCase:=False
Next i
End Sub

[Jakobshavn]

I would put the 400 words in their own worksheet. In my example, in a sheet called "words" in A1 thru OJ1.
These are read into a 2D array in VBA. Another sheet called "data" is activated. This sheet contains the data to be modified. Each of the cells in ActiveSheet.UsedRange is examined and if one of the "words" is found, it is replaced by a single space:

Please Login or Register  to view this content.