Delete rows using multiple String search

[BradleyS]

I have this code which works great finding a single string text value "9M0*" and if it exists it deletes the entire row.
i.e. If LCase(.Value) Like LCase("9M0*") Then .EntireRow.Delete

However, now I need to find multiple string values "9M0*", "9NU1*" to do the same task, but can't find a way to perform the string search on both or more than one value.

I tried the below, but it will not pick up the string unless it is an exact match, and I need to use a wild card
Select Case .Value
Case Is = "9M0*", "9NU1*": .EntireRow.Delete
End Select

Here is my current working code using :

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long

       'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        
        'Loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1
            
           'Check the values in the C column
            With .Cells(Lrow, "C")
                
                If Not IsError(.Value) Then
                    If LCase(.Value) Like LCase("9M0*") Then .EntireRow.Delete
                End If

            End With
        Next Lrow
    End With

 

[Peter_SSs]

1. About how many of these text strings are there?

2. About how many rows of data do you have to check through?

3. What version of Excel are you using?

4. Is there a heading row?

 

[BradleyS]

1. About how many of these text strings are there? Could be 1 to 100
2. About how many rows of data do you have to check through? Could be around be 60,000
3. What version of Excel are you using? xl2010
4. Is there a heading row? Yes

 

[My Aswer Is This]

Where are these values stored. The script must know where to look for these:
About how many of these text strings are there? Could be 1 to 100

 

[BradleyS]

As mentioned in the code example I provided that works for finding a single string. It looks in each cell within column C.
There is no fixed number of different strings to search, it can range from 2 to 100 different strings.
I can search for multiple strings using this code:

 Select Case .Value
 Case Is = "9M0*", "9NU1*": .EntireRow.Delete
 End Select

However, it doesn't seem to like the wildcard symbols as it only works if I enter an exact code in the string. The string is made up of a max 7 characters including numbers and letters. Therefore you can have 9, 9M, 9M0, 9M00, 9M000, etc.... Which is why I just want to be able to search with a wildcard symbol, i.e. 9M0*

 

[My Aswer Is This]

Yes I understand your looking for numerous strings. Your going to need to put these values in a range so we can search for them.

For example create a sheet named "Strings"
Then put the strings to search for in column "A" of sheet "Strings"
Like this: Could be 1 to 100 or more

Dog
Cat
Mouse

Then if you have "My Dog loves to go outside" in your original sheet column(C) that row will be deleted.

 

[BradleyS]

Here are a few rows of my spreadsheet and I would like to delete all rows that start with 9M0 and 9NU0 shown in column C

Excel 2010
	A	B	C
1	ID	eventdate	code
2	1	2016-02-29	9M1.
3	3	2016-01-29	9M0.
4	30	2016-02-29	9M0.
5	61	2015-12-01	9M10
6	69	2016-02-01	9M00
7	20420	2009-06-25	9NU1
8	20984	2003-08-13	9NU1
9	23378	2006-01-24	9NU1
10	23379	2011-05-23	9NU0
Data

 

[Peter_SSs]

You said there could be up to about 100 of the strings to search for.
The code for doing the job for 2 strings like your current sample could be quite different to the code to deal with 100 strings, so we need to know more about these strings, in particular how we get them written into the code.
Hence the question from MAIT about where the strings are stored, if they are stored somewhere.

Are they in a column on a worksheet somewhere?
- If so, what worksheet, what column, starting at what row?
- If not, how is the code going to know what strings you are looking for? Are we going to manually write them all into the code like you were attempting to do in post #1? If so, please give us the full list (or say 10 of them so we can have a better idea of any variety among them).

 

[My Aswer Is This]

If you were to put all your strings to search for in:
Sheet(2) Column ("A") starting in Row (1)
And run this script from Sheet(1)
I believe you would get what you want.
Until you check back in we will not know for sure.
And Peter may have a faster way.

Sub Remove_My_Rows()
Application.ScreenUpdating = False
Dim Arr() As Variant
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Arr = Sheet2.Range("A1:A" & Lastrowa)
Dim R As Long
Dim C As Long
Dim i As Long

    For i = Lastrow To 1 Step -1

    For R = 1 To UBound(Arr, 1)  ' First array dimension is rows.
        
        For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
            
            If InStr(Cells(i, 1).Value, Arr(R, C)) Then
                Range("F" & i).EntireRow.Delete
           
           End If
    Next C
Next R
Next
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Reading through the thread again, it is possible there has been a misunderstanding.
When you say you have lots (up to 100) of these strings to test, do you mean values like

a) "9M0*", "9NU0*", "ABC*", "56FR*", etc where there up to 100 different starting strings?

or

b) Do you mean like "9M05678", "9M065DR", "9M0HGY6", "9NU06T5", "9NU0444" etc, where there are only 2 starting strings ("9M0" & "9NU0") but up to 100 actual strings?