How to remove entire rows that contains a range of words

[nicoan]

Hello guys

Say I have the following sheet:

Column A
blue car
green car
red car
cyan car
magenta car
black car
yellow car
brown car
white car
violet car

The sheet have content in the column B and C irrelevant to this case. And in column D I have a range of words like this:

Column D
blue
violet
black

I need the cells content on the column D to be used as filters to strip all the rows (from A, B and C) that contains them. So the result will be:

Column A
green car
red car
cyan car
magenta car
yellow car
brown car
white car


I hope is clear enough. Can you please help?

Thanks in advance.

 

[Ron Coderre]

Try setting up this example:
On Sheet1
A1: Description and A2:A11 contains your list
Description
blue car
green car
red car
cyan car
magenta car
black car
yellow car
brown car
white car
violet car



On Sheet2
A1: Words and A2:A4 contains your list of words
Words
blue
violet
black

B1: Description <---This must match the column heading on Sheet1
B2: ="*"&A2&"*"

Copy that formula down through B4.
They will display this way:
Description
*blue*
*violet*
*black*

Note: The asterisks are wildcards that match anything before and after the text

Back to Sheet1
• Select A1:A11
• Data.Advanced_filter....If prompted: Click: OK
...List Range: $A$1:$A$11 <---this will already be selected
...Criteria Range: <--Click the Sheet2 tab and select B1:B4
...Click: OK

• Select the visible cells (excluding the heading)...so you'll be selecting A2:A11..which actually selects A2, A7, A11
• Home.Delete.Delete_sheet_rows
• Data.Clear_filter

The end result will be:
Description
green car
red car
cyan car
magenta car
yellow car
brown car
white car


Is that something you can work with?

 

[hiker95]

nicoan,

Welcome to the MrExcel forum.

Sample raw data in worksheet Sheet1 (the raw data in columns A, B, and C, can have a different number of rows):

Excel 2007
	A	B	C	D
1	blue car	black car	yellow car	blue
2	green car	blue car	white car	violet
3	red car	brown car	violet car	black
4	cyan car	cyan car	red car
5	magenta car	green car	magenta car
6	black car	magenta car	green car
7	yellow car	red car	cyan car
8	brown car	violet car	brown car
9	white car	white car	blue car
10	violet car	yellow car	black car
11
Sheet1

After the macro:

Excel 2007
	A	B	C	D
1	green car	brown car	yellow car	blue
2	red car	cyan car	white car	violet
3	cyan car	green car	red car	black
4	magenta car	magenta car	magenta car
5	yellow car	red car	green car
6	brown car	white car	cyan car
7	white car	yellow car	brown car
8
9
10
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub RemoveSearchStrings()
' hiker95, 04/06/2013
' http://www.mrexcel.com/forum/excel-questions/695653-how-remove-entire-rows-contains-range-words.html
Dim a As Variant, b As Variant, s As Variant, ss As Long
Dim i As Long, ii As Long, iii As Long, c As Long, lr As Long
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 4).End(xlUp).Row
  s = .Cells(1, 4).Resize(lr).Value
  For c = 1 To 3 Step 1
    lr = .Cells(Rows.Count, c).End(xlUp).Row
    a = .Cells(1, c).Resize(lr)
    ReDim b(1 To UBound(a, 1), 1 To 1)
    iii = 0
    For i = 1 To UBound(a, 1)
      ss = 0
      For ii = 1 To UBound(s, 1)
        If InStr(a(i, 1), s(ii, 1)) > 0 Then
          ss = ss + 1
        End If
      Next ii
      If ss = 0 Then
        iii = iii + 1
        b(iii, 1) = a(i, 1)
      End If
    Next i
    .Cells(1, c).Resize(UBound(b, 1)) = b
    Erase a: Erase b
  Next c
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveSearchStrings macro.

 

[nicoan]

Thank you both very much for your replies.

Hiker95, the macro seems to work partially, as the columns B and C seems to be untouched. Now the cells from column A doesn´t correspond with the rest of the columns.
In your example seems to work because the filter words are also there. But column B and C have a completely different content.

Can you please fix it so that it removes the whole rows (excepting the filter column)?

Thanks again.

 

[hiker95]

nicoan,

So that we can get it right the next time, can we have a screenshot of the raw data in columns A, B, C, D , and, a screenshot of what the results should look like (manually formatted by you).

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[hiker95]

nicoan,

I think I now understand what you are trying to accomplish.

Sample raw data in worksheet Sheet1:

Excel 2007
	A	B	C	D
1	blue car	blue car	blue car	blue
2	green car	green car	green car	violet
3	red car	red car	red car	black
4	cyan car	cyan car	cyan car
5	magenta car	magenta car	magenta car
6	black car	black car	black car
7	yellow car	yellow car	yellow car
8	brown car	brown car	brown car
9	white car	white car	white car
10	violet car	violet car	violet car
11
Sheet1

After the updated macro:

Excel 2007
	A	B	C	D
1	green car	green car	green car	blue
2	red car	red car	red car	violet
3	cyan car	cyan car	cyan car	black
4	magenta car	magenta car	magenta car
5	yellow car	yellow car	yellow car
6	brown car	brown car	brown car
7	white car	white car	white car
8
9
10
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub RemoveSearchStringsV2()
' hiker95, 04/06/2013
' http://www.mrexcel.com/forum/excel-questions/695653-how-remove-entire-rows-contains-range-words.html
Dim a As Variant, b As Variant, s As Variant, ss As Long
Dim i As Long, ii As Long, iii As Long, lr As Long
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 4).End(xlUp).Row
  s = .Cells(1, 4).Resize(lr).Value
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  a = .Cells(1, 1).Resize(lr, 3)
  ReDim b(1 To UBound(a, 1), 1 To 3)
  For i = 1 To UBound(a, 1)
    ss = 0
    For ii = 1 To UBound(s, 1)
      If InStr(a(i, 1), s(ii, 1)) > 0 Then
        ss = ss + 1
      End If
    Next ii
    If ss = 0 Then
      iii = iii + 1
      b(iii, 1) = a(i, 1)
      b(iii, 2) = a(i, 2)
      b(iii, 3) = a(i, 3)
    End If
  Next i
  .Cells(1, 1).Resize(UBound(b, 1), UBound(b, 2)) = b
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveSearchStringsV2 macro.

 

[nicoan]

Ahh, Perfect. You rock!

Best wishes.

 

[hiker95]

nicoan,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.