macro - search and delete

[jsabina]

Hi,
I was trying to do a macro to find a value in a column and delete it from every row.

This is my code:

Dim FoundCell As Range
Application.ScreenUpdating = False
Set FoundCell = Range("C:C").Find(what:="Mobile")
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Delete
Set FoundCell = Range("C:C").FindNext
Loop

In this way I delete all the rows where in the column C there is the word "Mobile".

It's working properly... the problem is that I have many different words to look for and for the moment I am just adding and readding lines of code.
There is a better way???

Now my code is like that:

Dim FoundCell As Range
Application.ScreenUpdating = False
Set FoundCell = Range("C:C").Find(what:="Mobile Device")
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Delete
Set FoundCell = Range("C:C").FindNext
Loop

Set FoundCell = Range("C:C").Find(what:="Supply")
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Delete
Set FoundCell = Range("C:C").FindNext
Loop

Set FoundCell = Range("C:C").Find(what:="Blackberry")
Do Until FoundCell Is Nothing
FoundCell.EntireRow.Delete
Set FoundCell = Range("C:C").FindNext
Loop


The problem is that I can have 20 or 30 words...
Any help will be appreciated.
Thanks
Jsabina

[jsabina]

A quick tip?
If I need to use another kind of function and not Find
I am looking around but I cannot find a solution.
I am beginner..

thanks!!

[arlu1201]

Welcome to the forum.

It would be good if you post your code within code tags in the future.

You can list the words in one column of your macro file and then use a loop to go through each word and perform the necessary actions. This way, you wouldnt need to code it again and again.

Let me know if you need more help with this.

[jsabina]

Hi Arlu,
thanks and sorry if I posted without the code tags.
So do you mean using always the find?

But using some if statements can I do the same?
I mean, can I cicle on all the table and check the status and if it contains one of the words I delete, if not I go to the next one?

I know how I would do that in php, with an array and if statements... not sure in VBA.

thanks!

[arlu1201]

I have 2 sheets - in sheet1, i have put some words starting from A1 to A5. In sheet 2, i have a list of words in column C. I have used this code for this example - you can adapt it to your situation -

Please Login or Register  to view this content.

It will automatically take the words 1 by 1 from sheet1 and locate them in sheet2 and then delete the rows where the words are found.

[smuzoen]

If the words occur many times in the Column then you would need to continue searching the column for any other rows that contain the word. Arlette's code works well if there is a single occurence of the word however if it appears many times then if would not delete all the rows. For interests sake the following will search for ALL occurences of the works, sorts the array with the rows numbers using a bubble sort then deletes the rows. It involves more code simply because it is looking for e.g. the word Apple being in column A 5 times.

Please Login or Register  to view this content.

Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[jsabina]

Hello!
Thanks very much for the help!
As smuzoen said there are more than one occurence for every word... so I tried the second code and it's working!

Now I would like to have an "half way"... the second code, but I'd like to take the array from a column in a second worksheet of my file called categories.

I am trying this code but I have an error message "mismatch"...
I am trying to study how to create arrays and so on... in the meanwhile if you can see the error would be really nice!!
thanks!!!

Please Login or Register  to view this content.

[jsabina]

Please Login or Register  to view this content.

[smuzoen]

I was not sure if you sorted it - If you place the list of words in column A in sheet 2 that you want to search for you can create an array from this - then compare this list of key words against Column A in sheet 1 that is the list that you want to check for keywords

Please Login or Register  to view this content.

If you need any help or other questions just ask.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[jsabina]

Thanks Anthony,
not really because I always get some kind of error
With your code I whave error on this line

Please Login or Register  to view this content.

Application defined or object defined error

[Steffen Thomsen]

Wow, this seem alot of code for the job.

Please Login or Register  to view this content.

No more is needed and even less if you have your validation list in the same workbook or a txt file

[smuzoen]

Try the attached workbook - if you can get Steffens code to work by all means shorter code is better. Anyway see attached workbook.