Delete rows when specific words and criteria are found

man · Jul 26, 2010

Hi,

I have a worksheet that contains 2 columns of data. I have listed a sample of the data as below column A and column B

...............Column A....................................Column B
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table
Row 5......bigtalltree.net...............................big tall tree

I have some questions on how to do the things I want.

1) I want to excel to search the whole document and detect rows with cells that start with the word 'black' and cells that start with the word 'big'. Delete the whole rows.

results to return will be
Row 3......thebigshortdog.net........................the big short dog

2) I want to search Column A only, delete the rows if the cells in Column A ends with '.net'

results to return will be
Row 1......blacktalldog.com..........................black tall dog
Row 2......blackhungrymouse.com................black hungry mouse

3) I want to detect cells that contain the word tall. If the word tall is detected in Column A or Column B, delete the row.

results to return will be
Row 2......blackhungrymouse.com................black hungry mouse
Row 3......thebigshortdog.net........................the big short dog
Row 4......bigblacktable.net..........................big black table

Thanks

Peter_SSs · Jul 28, 2010

1. I am ok with anything as long as I can get the results.
OK, I can work with that.

2. May I know what are the difference? I think a single final result will do.
The difference is whether I have to write (and you have to run) three different macros like I posted in post #12 or whether we combine them into one single macro. The only reason you would leave them separate is if sometimes you want to delete, for example, the big/black/tall rows but not the '.net' rows

3. I would like the rows to be greyed, and then automatically or manually arrange the greyed rows to the bottom. (If possible, I would like have an option to change it to delete by editing the codes myself)
With Excel 2002, sorting the grey columns to the bottom is a more complex task. It could be done but are you sure you really need to do this?

4. I am not really sure how to retrive the the macro in the workbook. I can only see a Macro name: Sheet1.Test
So where did you paste my macros and how did you test them?

man · Jul 28, 2010

Peter_SSs said:
1. I am ok with anything as long as I can get the results.
OK, I can work with that.

2. May I know what are the difference? I think a single final result will do.
The difference is whether I have to write (and you have to run) three different macros like I posted in post #12 or whether we combine them into one single macro. The only reason you would leave them separate is if sometimes you want to delete, for example, the big/black/tall rows but not the '.net' rows

I think separate should be better. What is your suggestion? But I am not sure how to run three different macros. In my real worksheet, I have thousands of rows, I need to filter out more words, (example, filter out cells that start with black, big, blue, my, red, yellow, the.... and filter out cells that end with cats, dogs, chairs, fan, lights, zone before the . domain extension). So I need to add a lot more macros by myself or maybe is there an option I can input more words into one macro?

Peter_SSs said:
3. I would like the rows to be greyed, and then automatically or manually arrange the greyed rows to the bottom. (If possible, I would like have an option to change it to delete by editing the codes myself)
With Excel 2002, sorting the grey columns to the bottom is a more complex task. It could be done but are you sure you really need to do this?

My main reason of doing this is that I need to look at the filtered data only. If the greyed rows are unmoved and are in between the ungreyed rows, it makes it very difficult to see the data I want. I want to move the greyed rows to the bottom just to keep it for reference purpose. If it is a very complex task, is there any other options that you can suggest?

Peter_SSs said:
4. I am not really sure how to retrive the the macro in the workbook. I can only see a Macro name: Sheet1.Test
So where did you paste my macros and how did you test them?

This is how I run the macro > I opened the worksheet in my excel, right click on the sheet tab and click view code. The Microsoft Visual Basic window pops up, I copy and paste in the code you provided in post #12, press F5, then clicked the Run on the menu, then clicked the RunSub/UserForm

Did I perform the run correctly? Do I need to run 3 times or something? I did not save anything and I am not sure what to save.

Thanks

Peter_SSs · Jul 28, 2010

man said:
I think separate should be better. What is your suggestion? But I am not sure how to run three different macros. In my real worksheet, I have thousands of rows, I need to filter out more words, (example, filter out cells that start with black, big, blue, my, red, yellow, the.... and filter out cells that end with cats, dogs, chairs, fan, lights, zone before the . domain extension). So I need to add a lot more macros by myself or maybe is there an option I can input more words into one macro?

How big is the list of words? 20 or 100 or 1000? Just trying to get a rough idea. Is the list of words in the sheet somewhere, or do you just have it in your head or on paper?

The fact that there are more than a couple of words means that my method of using AutoFilter will probably not be the most appropriate method.

man said:
My main reason of doing this is that I need to look at the filtered data only. If the greyed rows are unmoved and are in between the ungreyed rows, it makes it very difficult to see the data I want. I want to move the greyed rows to the bottom just to keep it for reference purpose. If it is a very complex task, is there any other options that you can suggest?

Should be able to get the grey rows at the bottom.

man said:
This is how I run the macro > I opened the worksheet in my excel, right click on the sheet tab and click view code. The Microsoft Visual Basic window pops up, I copy and paste in the code ...

I should have picked up on this earlier. I think the code is not in the correct place. Right click the sheet name tab and choose 'View Code'. Then in the VB window use the menus to Insert|Module. That is where to paste the code. Then when you close the VB window you can access the macros by
Tools|Macro|Macros...|select the macro|Run
or by keyboard
Alt+F8|select the macro|Run

man · Jul 28, 2010

Peter_SSs said:
How big is the list of words? 20 or 100 or 1000? Just trying to get a rough idea. Is the list of words in the sheet somewhere, or do you just have it in your head or on paper?

The fact that there are more than a couple of words means that my method of using AutoFilter will probably not be the most appropriate method.

I have a list of words I want filter, e.g. 1. cell starting with WORD, 2. cell ends with .com / .net /.org, 3. WORD detected anywhere in cells, 4. cell ends with WORD.domain extension (note: WORD is the list of words)

The list will be around 200 or more. I have not written the list or typed it in a sheet yet, I plan write down the list somewhere, I may add more words to the list in future.

Peter_SSs said:
I should have picked up on this earlier. I think the code is not in the correct place. Right click the sheet name tab and choose 'View Code'. Then in the VB window use the menus to Insert|Module. That is where to paste the code. Then when you close the VB window you can access the macros by
Tools|Macro|Macros...|select the macro|Run
or by keyboard
Alt+F8|select the macro|Run

I followed the steps. When I go to Tools|Macro|Macros...|select the macro|Run, I see 3 macro names BlackBigRows, DotNetRows, TallRows, and I run each of them. (I repeat the process 3 times). I think this will give me the final result.

kpark91 · Jul 28, 2010

Peter_SSs said:
This code would only deal with rows that actually end with 'days' so it would not pick up 'elevengooddays.com' for example.

From the earlier description, I think what is required is to identify rows that have days immediately before the '.' so the criteria would be
"*days.*"

Hi, Peter. I thought *days would pick it up since
"man" has the same exact wording in column B without the '.'

Peter_SSs · Jul 28, 2010

man said:
I have a list of words I want filter, e.g. 1. cell starting with WORD, 2. cell ends with .com / .net /.org, 3. WORD detected anywhere in cells, 4. cell ends with WORD.domain extension (note: WORD is the list of words)

I'm still struggling a bit with the overall deal here in relation to your requirements and sample data. You say the data in post #13 is more representative of your data. You also now say one of your four conditions is rows that end in .com/.net/.org. From your sample data, that would be all the rows, so there would be no need for the other three conditions to even be tested. Is the data really more varied again?

The reason I am pressing for realistic represenatative data and requirements is so I do not spend (waste) time developing a solution that works on unrealistic data/requirements but not on the real data/requirements. To some extent that already happened when I thought there were only two 'starting words' (big/black) so I developed a suggestion using AutoFilter. However, that method is not the best method if there are many 'starting words'.

man · Jul 28, 2010

I am sorry if I have confused you. Initially I was thinking that I could add more starting words, or edit the .net by myself. Initially I did not know about VBA codes and I thought there might be something that works like the search and replace, and I can input multiple words I want to detect. I have mentioned only .net because most of the time I would find the .net rows. Sometimes I will need to find .com or .org. and I thought I would be able do that by replacing .net and do a search again. I understand it would mean all the rows, but I am not going to find all the three different extension at the same time. The sample data is realistic.

Peter_SSs · Jul 29, 2010

OK, see if this is any use. First test on a sample workbook set up as follows:

1. A sheet named 'Main' with just the data as set out in post #13 except moved down one row. So the data is in A2:B16. If you haven't already got some, put some headings in A1:B1

2. I have assumed no other data on this sheet.

3. On a second sheet in the same workbook, name the sheet 'Exclude Lists'

4. Set up your exclusion categories with headings as shown. (You could use different headings but they should not have any spaces or unusual characters in them.)

Excel Workbook

A

B

C

D

1

Start

End

Anywhere

Before_Dot

2

big

.net

tall

days

3

black

4

Exclude Lists

Paste the code below into a new Module (as before: Insert|Module) and run it.

 Sub man() Dim wsMain As Worksheet, wsExclude As Worksheet Dim rMain As Range, rExclude As Range Dim aExclHead, sFormulas Dim nName As Names Dim i As Long, lr As Long, lExclCategories As Long Application.ScreenUpdating = False Set wsMain = Sheets("Main") Set wsExclude = Sheets("Exclude Lists") Set rExclude = wsExclude.Range("A1").CurrentRegion aExclHead = rExclude.Rows(1).Value lExclCategories = UBound(aExclHead, 2) With wsMain .UsedRange.Offset(, 2).ClearContents .AutoFilterMode = False Set rMain = .Range("A1").CurrentRegion.Resize(, 1) End With sFormulas = Array( _ "=IF(ISNUMBER(MATCH(LEFT(B1,FIND("" "",B1&"" "")-1)," & aExclHead(1, 1) & ",0)),1,"""")", _ "=IF(ISNUMBER(MATCH(REPLACE(A1,1,FIND(""."",A1&""."")-1,"""")," & aExclHead(1, 2) & ",0)),1,"""")", _ "=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" & aExclHead(1, 3) & ",B1))),1,"""")", _ "=IF(ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(B1,"" "",REPT("" "",100)),100))," & aExclHead(1, 4) & ",0)),1,"""")") For i = 1 To lExclCategories lr = wsExclude.Cells(Rows.Count, i).End(xlUp).Row lr = IIf(lr < 2, 2, lr) On Error Resume Next ActiveWorkbook.Names(aExclHead(1, i)).Delete On Error GoTo 0 ActiveWorkbook.Names.Add Name:=aExclHead(1, i), RefersToR1C1:= _ "='Exclude Lists'!R2C" & i & ":R" & lr & "C" & i With rMain.Offset(, 1 + i) .Formula = sFormulas(i - 1) .Value = .Value End With Next i With rMain.Offset(, 2 + lExclCategories) .FormulaR1C1 = "=IF(COUNT(RC3:RC[-1]),1,0)" .Cells(1, 1).Value = "Selected" .Value = .Value End With rMain.Offset(, 2).Resize(1, lExclCategories).Value = aExclHead With wsMain.UsedRange .Sort Key1:=.Cells(2, 3 + lExclCategories), Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal .Offset(, .Columns.Count - 1).Resize(, 1).Replace What:="0", Replacement:="" .AutoFilter End With Application.ScreenUpdating = True MsgBox "Done" End Sub >

It should put a '1' under the correct heading for each row that meets that exclusion category. The final column ('Selected') should show a '1' if the row meets any of the exclusion categories. All these rows that meet any exclusion category shold be at the bottom of the data (though I haven't shaded them grey).

The code also puts AutoFilter on the sheet so you can just click (for example) the drop-down in the final column to just show '1' (or non-blanks) if you want to see the selected rows. (You could also quickly select and delete the rows at this point while filtered if you want). You can filter any of the other columns to see which rows met (or didn't meet) the exclusion criteria for that column.

Try changing/adding/deleting values in the 'Exclude Lists' sheet and run the code again.

You could add more exclusion categories but the 'sFormulas = ' section of the code would need to be expanded for whatever the new category is.

Here is my 'Main' sheet after the code was run. Remember it started like post #13 (with headings).

Excel Workbook

A

B

C

D

E

F

G

1

Heading 1

Heading 2

Start

End

Anywhere

Before_Dot

Selected

2

tenbigdogs.com

ten big dogs

3

thirteennicetables.com

thirteen nice tables

4

myrowsixbigcats.com

my row six big cats

5

fourteendayssmallcats.com

fourteen days small cats

6

fifteendaysblackcats.com

fifteen days black cats

7

elevengooddays.com

eleven good days

1

8

twelvesleepydays.org

twelve sleepy days

1

9

thebigshortdog.net

the big short dog

1

10

blacktalldog.com

black tall dog

1

11

blackhungrymouse.com

black hungry mouse

1

12

bigblacktable.net

big black table

1

13

bigtalltree.net

big tall tree

1

14

bluetallhouseseven.net

blue tall house seven

1

15

eighttalltables.com

eight tall tables

1

16

blackninecats.com

black nine cats

1

17

Main

man · Jul 29, 2010

The code works great and its easy to use. Thanks

I have some problems.

When I input 2 words in the Start or Before_Dot columns in the Exclude Lists, it will not work. For example under Start column, I entered 'thirteennice or 'thirteen nice', under Before_Dot, I entered 'bigcats' or 'big cats'

The Anywhere column in the Exclude Lists cannot be blank/empty for all cells. If it is blank, after running the macro the Anywhere column in the Main sheet will be filled with 1

One question, does the code scan through or need column B data?

Peter_SSs · Jul 29, 2010

man said:
The code works ...

I have some problems.

Hmm, sounds a bit contradictory.

man said:
When I input 2 words in the Start or Before_Dot columns in the Exclude Lists, it will not work. For example under Start column, I entered 'thirteennice or 'thirteen nice', under Before_Dot, I entered 'bigcats' or 'big cats'

If you followed the sample as suggested, there were two values (big, black) under Start. In my final screen shot after running the code five rows are marked in the Start column - 3 that started with black and 2 that started with big. Did you get different results than me with the same sample data?

man said:
The Anywhere column in the Exclude Lists cannot be blank/empty for all cells. If it is blank, after running the macro the Anywhere column in the Main sheet will be filled with 1

Yes, my mistake. I thought I had tested that but must not have. The Anywhere column in the Exclude Lists sheet still needs to keep its heading, even if there is no other data below that heading.

Put another Dim statement with the others at the top of the code

Code:

Dim bListExists As Boolean

Replace the section of code in the middle with this:

 For i = 1 To lExclCategories lr = wsExclude.Cells(Rows.Count, i).End(xlUp).Row bListExists = (lr > 1) On Error Resume Next ActiveWorkbook.Names(aExclHead(1, i)).Delete On Error GoTo 0 If bListExists Then ActiveWorkbook.Names.Add Name:=aExclHead(1, i), RefersToR1C1:= _ "='Exclude Lists'!R2C" & i & ":R" & lr & "C" & i With rMain.Offset(, 1 + i) .Formula = sFormulas(i - 1) .Value = .Value End With End If Next i

man said:
One question, does the code scan through or need column B data?

Yes it does.

Delete rows when specific words and criteria are found

man

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Excel Facts

man

Board Regular

Peter_SSs

MrExcel MVP, Moderator

man

Board Regular

kpark91

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

man

Board Regular

Peter_SSs

MrExcel MVP, Moderator

man

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Delete rows when specific words and criteria are found

Board Regular

MrExcel MVP, Moderator

Excel Facts

Board Regular

MrExcel MVP, Moderator

Board Regular

Well-known Member

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock