Delete row using array, don't delete on partial match

[sandcastl3s]

I have a text file with thousands of lines, one word per line. I'm trying to delete/remove lines that do not include one of multiple values, but not delete if there is a partial match of one of the values.
This almost exactly what I'm after:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#00008B]
Sub[/COLOR][COLOR=#1A1A1A] Main[/COLOR][COLOR=#1A1A1A]()[/COLOR][COLOR=#1A1A1A]

[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#1A1A1A] dontDelete
dontDelete [/COLOR][COLOR=#1A1A1A]=[/COLOR][COLOR=#1A1A1A] Array[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#6B291B]"Will, "Jim[/COLOR][COLOR=#6B291B]"[/COLOR][COLOR=#1A1A1A])[/COLOR][COLOR=#1A1A1A]

[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#1A1A1A] i [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=#1A1A1A],[/COLOR][COLOR=#1A1A1A] j [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=#1A1A1A]

[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#1A1A1A] isThere [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Boolean[/COLOR][COLOR=#1A1A1A]

[/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#1A1A1A] i [/COLOR][COLOR=#1A1A1A]=[/COLOR][COLOR=#1A1A1A] Range[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#6B291B]"A"[/COLOR][COLOR=#1A1A1A]&[/COLOR][COLOR=#1A1A1A] Rows[/COLOR][COLOR=#1A1A1A].[/COLOR][COLOR=#1A1A1A]Count[/COLOR][COLOR=#1A1A1A]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#1A1A1A]xlUp[/COLOR][COLOR=#1A1A1A]).[/COLOR][COLOR=#1A1A1A]Row [/COLOR][COLOR=#00008B]To[/COLOR][COLOR=#6B291B]1[/COLOR][COLOR=#00008B]Step[/COLOR][COLOR=#1A1A1A]-[/COLOR][COLOR=#6B291B]1[/COLOR][COLOR=#1A1A1A]
    [/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#1A1A1A] j [/COLOR][COLOR=#1A1A1A]=[/COLOR][COLOR=#1A1A1A] LBound[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#1A1A1A]dontDelete[/COLOR][COLOR=#1A1A1A])[/COLOR][COLOR=#00008B]To[/COLOR][COLOR=#1A1A1A] UBound[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#1A1A1A]dontDelete[/COLOR][COLOR=#1A1A1A])[/COLOR][COLOR=#1A1A1A]
        [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#1A1A1A] StrComp[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#1A1A1A]Range[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#6B291B]"A"[/COLOR][COLOR=#1A1A1A]&[/COLOR][COLOR=#1A1A1A] i[/COLOR][COLOR=#1A1A1A]),[/COLOR][COLOR=#1A1A1A] dontDelete[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#1A1A1A]j[/COLOR][COLOR=#1A1A1A]),[/COLOR][COLOR=#1A1A1A] vbTextCompare[/COLOR][COLOR=#1A1A1A])[/COLOR][COLOR=#1A1A1A]=[/COLOR][COLOR=#6B291B]0[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#1A1A1A]
            isThere [/COLOR][COLOR=#1A1A1A]=[/COLOR][COLOR=#6B291B]True[/COLOR][COLOR=#1A1A1A]
        [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#1A1A1A]
    [/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#1A1A1A] j
    [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#00008B]Not[/COLOR][COLOR=#1A1A1A] isThere [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#1A1A1A]
        Range[/COLOR][COLOR=#1A1A1A]([/COLOR][COLOR=#6B291B]"A"[/COLOR][COLOR=#1A1A1A]&[/COLOR][COLOR=#1A1A1A] i[/COLOR][COLOR=#1A1A1A]).[/COLOR][COLOR=#1A1A1A]Delete shift[/COLOR][COLOR=#1A1A1A]:=[/COLOR][COLOR=#1A1A1A]xlUp
    [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#1A1A1A]
    isThere [/COLOR][COLOR=#1A1A1A]=[/COLOR][COLOR=#6B291B]False[/COLOR][COLOR=#1A1A1A]
[/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#1A1A1A] i

[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]Sub

[/COLOR]</code>

except it looks for exact matches in the array. If the array is ("Will", "Jim") then it reduces

Will
William
Jim
Bob
Jim
Jimmy
James

to

Will
Jim
Jim

This deletes the items that include but are not exact matches of the values in the array.

I'm trying to figure out how to not delete the row/line if it contains a value in the array, either as an exact match or a partial match...which would reduce the above example list to

Will
William
Jim
Jim
Jimmy

Any ideas? I'm a bit of an excel novice and I can't work it out for the life of me. Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you confirm what should happen to a line that contains these

Jo-Will

or

Swill

 

[sandcastl3s]

Hey, thanks Peter! I've read so many threads here over the years and never needed to ask a question. Finally signed up for this one!

In both your examples the lines would not be deleted as they contain a value in the array, albeit not an exact match.

Something like Jo-Wil or Swil would be deleted.

 

[Peter_SSs]

I've assumed that you only have data in column A. If that is the case, then give this a try in a copy of your workbook, otherwise post back with more details.

Sub Del_Rows()
  Dim a, b, dontDelete
  Dim i As Long, j As Long
  
  dontDelete = Array("Will", "Jim")
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    For j = LBound(dontDelete) To UBound(dontDelete)
      If InStr(1, a(i, 1), dontDelete(j), vbTextCompare) > 0 Then
        b(i, 1) = 1
        Exit For
      End If
    Next j
  Next i
  Application.ScreenUpdating = False
  With Range("A1").Resize(UBound(a), 2)
    .Columns(2).Value = b
    .Sort Key1:=.Columns(2), Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    On Error Resume Next
    .Columns(2).SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
    .Columns(2).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[sandcastl3s]

I've assumed that you only have data in column A. If that is the case, then give this a try in a copy of your workbook, otherwise post back with more details.

Peter, that worked flawlessly. I had figured out to change strComp to InStr but the other things you changed are well over my head.

You have my thanks Peter, and thanks for your warm welcome! I'll be recommending this forum going forward.

 

[Peter_SSs]

Peter, that worked flawlessly. I had figured out to change strComp to InStr but the other things you changed are well over my head.

You have my thanks Peter, and thanks for your warm welcome! I'll be recommending this forum going forward.

You are very welcome.

There are 2 main things in my code that make it much faster than what you had, and to consider with any future coding you do.

1. I read all the values into an array in memory and worked with them from there. That eliminates the code having to refer back to the worksheet for each row's value. Interactions between the code and the worksheet slow your code considerably.

2. Before deleting any rows, I sorted them together so there was just one single 'block' of rows to delete. Deleting a great number of disjoint rows is also a quite slow process.