Data Cleaning - VBA - Find specific

KJHEXCELL

New Member
Joined
Feb 9, 2019
Messages
6
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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
How about
Code:
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
 
Upvote 0
This worked very well! So thanks, I will add this to the find and delete Row part and its perfect!!

CHeers!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top