delete entire row if it doesn't contains at least 1 of the words listed

**4evra** · 01-30-2013, 06:01 AM

I've got a web query that daily gets about 8k rows of data.
I can filter out via filter in excel,but its slow,since i've got to keep data for about 50,60 entries.Rest of them i dont need.

So,a VBA or Macro that checks the row by row,then checks if that row contains one of a 60 words i pre-inputed,and then keeps those rows who got a match,while deleting any others.

**Mowgli** · 01-30-2013, 06:48 AM

4evra,

Would you be able to post a dummy section of workbook to show the layout etc. (no need for 8000 rows of data

)

Will the words be within a text string in a cell or will each word be the only thing in the cell?
Will the words always appear in the same column or will that change?

Mowgli

**4evra** · 01-30-2013, 07:01 AM

example.xlsx
Not each cell contains only 1 word.Some columns contain 2,maybe 3 words.
BUT,words i plan on checking are always 1 word(no spaces),and they are located inside "igrac" columns(which will be columns B and F)

So,i want the context of cells B and F to be crosschecked with some words i name in a list somewhere in excel,then,if one of those words is located in a specific row,that row is kept from deletion.

In that excel file,for example,my "name list" would contain words : "Schwaba","bob78"
Thus,when i execute the code,row 3 and row 6 would be kept in excel,since they contain those words.Other rows would be deleted.

**Mowgli** · 01-30-2013, 11:02 AM

4evra,

I am still quite the novice at VBA (this is my first post using variables even, however I think this will carry out the job you want it to do. Am happy for anyone to modify and tidy it up some though

Please Login or Register  to view this content.

The first section is where you list the variables and what their values will be, I only included 4 but that can be expanded
In each section for checking the columns B & F data I have only included the first 2 words however you can expand this out also
The range where you keep the list of names can be changed however at present it looks at column A in sheet 2

Hope this is of some help,

Mowgli

**JOHN H. DAVIS** · 01-30-2013, 01:17 PM

Another to consider. Also relys on Sheet2 as having the list.

Please Login or Register  to view this content.

**4evra** · 01-30-2013, 01:57 PM

@mowgli
Hmm,i input data into A fields on sheet2,but after i execute the macro,all entries get deteled.
I tested on the example excel.
Tried manually inputing words.Same thing.

I see that u mark the rows for keeping by "keep" tag.
I deleted the part of code that deletes rows,just to see if the 'keep' tag gets inputed into L column.
And it doesnt.
Not sure why though,code i clearly ok,since debugger doesn't throw up any errors for all.
I'll look into it more closely once i get home and then i'll post some more info about the whole thing

@John
Your code works

Many thanks to both of you

p.s. even though i've got solution to my problem,i'm still gonna try to brainstorm that code of yours mowgli,just to try to help you in your VBA learning

**JOHN H. DAVIS** · 01-30-2013, 02:11 PM

You're welcome.

**JOHN H. DAVIS** · 01-30-2013, 02:14 PM

You're welcome. Glad to help out, and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.