How to delete entire rows in Excel if column A contain text from external txt file

Question

Hello please help if you can.I have a text file called del.txt containing

STRING1   
STRING5

Also I have an excel sheet with almost 1 000 000 rows in it.
I'm looking for an EFFECTIVE way how to delete entire rows if text string in column A exactly matches one of text strings from del.txt file?

Text strings in excel file in a column A:

STRING1  entire row will be deleted   
STRING1  entire row will be deleted      
STRING3   
STRING3   
STRING5  entire row will be deleted

So in this case row with STRING1 and STRING5 will be deleted.Thank you for any suggestions.

So far I have this code below, but I don't know how to take values from external text file...

Sub Del_Strings()
Dim i As Long, strArr
strArr = Array("STRING1", "STRING2")
For i = LBound(strArr) To UBound(strArr)
    With [A1:A1000000]
        .Replace strArr(i), "", xlWhole
    End With
Next i
ActiveSheet.Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(4).EntireRow.Delete
End Sub

Please provide your code and what doesn't work in it. This site is meant to help users with their programming, not to do their work. — Julie Pelletier, May 27 '16 at 04:04

score 1 · Accepted Answer · answered May 27 '16 at 11:36

This should work for you:

Option Explicit

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

Sub Del_Strings()
    Dim MyData As String, strData() As String
    Dim i As Long, lastRow As Long

    Open "C:\test\test\del.txt" For Binary As #1    '--->change file path as required
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = lastRow To 1 Step -1
        If IsInArray(Cells(i, 1).Value, strData) Then 
            Rows(i).EntireRow.Delete
        End If
    Next i
End Sub

Ask me if you have any queries.

How to delete entire rows in Excel if column A contain text from external txt file

1 Answers1