Data Cleaning - VBA - Find specific

[KJHEXCELL]

Hi, again peeps.

I am trying to remove irrelevant data from a table, I am trying to include a .find string but cannot seem to locate it on VBA... and then loop into the next word from a chosen set of words

List of specific words to find: Hello, Goodbye and Select(makes things difficult...)

I don't think it will be a loop, more just the .find then select the found word and delete. But running them all at same time.

"Specific word".find

"Specific word".Select

Selection.EntireRow.Delete

"Next Word".find

"Next word".Select

Selection.EntireRow.Delete

"Select".Find (Select is the word)

"Select".Select

Selection.EntireRow.Delete

and etc...

Hope you can help! Many thanks!

 

[My Aswer Is This]

So when you say Table

Do you mean only search within the Table Range

Or do you mean search the entire sheet.

If it's a Excel Table what is the name of the Table

And what are or where can we find this list of names

 

[My Aswer Is This]

I gave you a answer to a previous post you posted today and you never did comment back if it worked for you. It's customary to answer back if a script provided worked or not.

Here is your previous post.
https://www.mrexcel.com/forum/excel...paste-loop-very-very-new-vba.html#post5223263

 

[KJHEXCELL]

Hi My answer is, check out my reply to another post so thank you!!

In this post, I mean the whole sheet. I was trying to construct a post which didn't hit these problems but i have failed haha.It

It's in the whole sheet yes, I'm just wondering the VBA command for find - because I think I'm on the right track.

Once I've got that I can just change the search word and apply to all the words that appear.

Hope you can help!

 

[KJHEXCELL]

I think the best way to do this so far, is more simply to use the remove duplicates and then remove them 1 by 1.

Is there a .find option or find function in VBA?

 

[Fluff]

How about

Sub kjhexcell()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Hello", "Goodbye", "Select")
   For i = 0 To UBound(Ary)
      Cells.Replace Ary(i), "", , , False, , False, False
   Next i
End Sub

 

[KJHEXCELL]

This worked very well! So thanks, I will add this to the find and delete Row part and its perfect!!

CHeers!

 

[Fluff]

You're welcome & thanks for the feedback