Need To Delete Rows Based On Multiple Criteria

[Andyronon]

Hi, I'm knew to this and would appreciate any help provided. I read somewhere that there is a limit to the number of terms that one macro could handle. I need a VBA script to delete multiple rows in column “A” based on if the cell has (ANY) reference of over 150 terms.

For example, if I want to delete the row that has the word “Ball” in it, three of the below five entries would be deleted.
Ball game
Sports Stadium
Softball game
Football game
Sports Fan

All that would remain is “Sports Stadium” and “Sports Fan”. I tried this script but got error messages

Sub Removal()
Dim i As Long, searchString As String

For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
' if a row is marked for deletion, delete it and continue.
If Left(Range("A" & i), 1) = "0" Then
Rows(i).Delete
' skip to next row
GoTo NextRow
End If

searchString = LCase(Range("A" & i))

If (InStr(1, searchString, "ball") > 0) Or _
(InStr(1, searchString, "number") > 0) Or _
(InStr(1, searchString, "minimum") > 0) Then
Rows(i).Delete
End If


Can someone help with this? What am I doing wrong?

 

[Fluff]

Hi & welcome to MrExcel.
The easiest way is to create a list of the values you want to delete & then use a formula to find the rows you don't want.
You can then use the autofilter to filter greater than 0 on that column to delete the rows.

For simplicity I've put it on the same sheet, but the list can go on another sheet

Book1
	A	B	C						I
1	District
2	Eden	0							West
3	High Peak	1							North
4	Bradford	0							east
5	Staffordshire Moorlands	0							South
6	Derbyshire Dales	0							high
7	Cheshire East	1							low
8	Calderdale	0
9	Oldham	0
10	West Somerset	1
11	Shropshire	0
12	County Durham	0
13	Newcastle-under-Lyme	0
14	Northumberland	1
15	Rossendale	0
Mon

Cell Formulas
Range	Formula
B2:B15	B2	=SUM(--ISNUMBER(SEARCH($I$2:$I$7,A2)))

 

[Andyronon]

Hi, I tried the formula but it isn’t working for me. I’m seeing a couple of problems and I will list them here.

1. When I enter a word in column “I” the number changes beside a different word in my column “A” list.

2. When I enter a word in column “I” the number only changes beside ONE instance of a word that appears in column “A”……(The number changes beside the wrong word). The words in my A column will be multiple entries. I the code to review the entire list and eliminate the correct word from the entire list. My lists can be 70,000 long.

I tried the solution out on a much smaller sample. Please see attached graphic.

 

[Fluff]

The part of the formula in red
=SUM(--ISNUMBER(SEARCH($I$2:$I$7,A2)))
Should only look at the list of values, you cannot have any blank cells in the range, so as you are only testing one value in that test the formula should be
=SUM(--ISNUMBER(SEARCH($I$2:$I$2,A2)))
and if you had values from I2 down to I100 you would use
=SUM(--ISNUMBER(SEARCH($I$2:$I$100,A2)))

 

[overclock]

Use an array to delete multiple terms at once.

Sub DeleteRowsWithText()
    Dim LRow As Long
    Dim x As Long
    Dim Arr As Variant
    Dim i As Long

'Macro Optimization
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

    Arr = Array("*ball*", "*Y*", "*Z*")
    LRow = Cells(Rows.Count, "A").End(xlUp).row
    For x = LRow To 1 Step -1
        For k = LBound(Arr) To UBound(Arr)
            If Cells(x, "L").Value Like Arr(k) Then
                Rows(x).Delete
            End If
        Next k
    Next x

'Turn off Macro Optimization
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[Fluff]

Wit over 150 terms to search for, that is likely to be very slow.

 

[overclock]

I agree. But both solutions are going to take some time to do. The macro will automate it. However if he is counting each word with "ball" (football, soccerball, softball) then yes he is looking for 150 different words. But if he wants to delete anything with ball then he can put wildcards around the word and rock on. That would make the process a lot easier.