VBA store words in array then delete them

[decadence]

Hi, I have lots of words that need removing from a column, the best way I can think is to use a hidden reference sheet that contains these words that I want to remove from another document, is it possible to look for each word in the hidden sheet and if found in the other document store it in the array for the words to be deleted all at the same time?.

I have tried the Find and Replace function, it's good but not really time efficient. I have also tried using an array of words in VBA but this does not allow me to capture all of the Find and Replace words.

There could be 10,000 words to be removed as there will be variations of each words e.g. Apples, Apple
Any help would be appreciated, Thanks.

 

[Akuini]

Hi,decadence
1. Can you post your code you already have? It works but you need it to be faster, right?
2. '10,000 words to be removed', in how many data rows is it?

 

[Fluff]

Also will the words exists on their own in the cell, or are they part of a larger string?

 

[decadence]

Hi Akuini, No I don't have the code yet, The amount of Rows are varied from each workbook opened.
Hi Fluff, The words could either be on their own or part of a string.

What I would like to do is Step through the cells Or use the whole column from the varied range, if any words from the hidden sheet are in the activeworkbook cells/column then store that word in the array, and repeat this until all words from the hidden sheet if found to replace the cells/column with nothing.
there will be 1 word to find and replace per cell in the hidden sheet in column A

 

[Fluff]

Which column do you want to remove the words from?
Do you want to remove just the found word, or clear the entire cell?
Do you have any formulae/formatting that needs to be retained?

 

[decadence]

Hi Fluff, Column C is the column for replacement, yes just the word found from the hidden sheet, no formulas or formatting, Also I want to put the hidden sheet in my custom add-in, however I'm not sure how to reference hidden sheet in this add-in, The VBAProject Name is Tools(Misc) and the sheetname is Lookup("Replace").

 

[Fluff]

Try

Sub DeleteWords()
   Dim Wary As Variant, Ky As Variant
   Dim r As Long
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Application.ScreenUpdating = False
   Set Ws1 = Sheets("[COLOR=#ff0000]New[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]Pcode[/COLOR]")
   Wary = Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp)).Value2
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Wary)
         .Item(Wary(r, 1)) = Empty
      Next r
      For Each Ky In .Keys
         Ws2.Range("C:C").Replace Ky, "", xlPart, , False, , False, False
      Next Ky
   End With
End Sub

Change sheet names to suit.

 

[decadence]

Sorry for late reply, this is brilliant. Thanks Fluff

 

[Rick Rothstein]

Try

Sub DeleteWords()
   Dim Wary As Variant, Ky As Variant
   Dim r As Long
   Dim Ws1 As Worksheet, Ws2 As Worksheet
   
   Application.ScreenUpdating = False
   Set Ws1 = Sheets("[COLOR=#ff0000]New[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]Pcode[/COLOR]")
   Wary = Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp)).Value2
   With CreateObject("scripting.dictionary")
[B][COLOR="#FF0000"]      For r = 1 To UBound(Wary)
         .Item(Wary(r, 1)) = Empty
      Next r
[/COLOR][/B]      For Each Ky In .Keys
         Ws2.Range("C:C").Replace Ky, "", xlPart, , False, , False, False
      Next Ky
   End With
End Sub

Change sheet names to suit.

Fluff, why did you store the contents of the Wary array in the dictionary only to read them out for replacement rather than just read them out from the Wary array directly for replacing?

Since the OP said "as there will be variations of each words e.g. Apples, Apple", how does your code protect against replacing say "Apple" from "Apples" thus leaving the "s" remaining? Are you relying on the OP having arranged the list from the longest word to the shortest word (which I think should work)?

 

[Fluff]

Hello Rick
Two very goods points and I've no idea why I did it like that, probably not enough coffee at the time