How do we modify program to remove unique values in cell and do not end of string with this value? We need remove rows from c3delete.txt file with unique values first second etc.. from excel c3 column in excel file***
Sub RemoveRowsRentruck()
Dim sh As Worksheet, N1 As Long, d1 As Long, arrCond, El, txtFileName As String
Dim objFSO As Object, objTxt As Object, rngDel As Range, strText As String
Set sh = ThisWorkbook.ActiveSheet 'use here the necessary sheet (not necessary to be activated)
sh.Cells.ClearFormats
txtFileName = ThisWorkbook.Path & "\" & "c3delete.txt" 'fill here the text file full name
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FileExists(txtFileName) Then 'check if the text file exists in the path
MsgBox "The text file does not exist on the path: """ & txtFileName & """."
Exit Sub
End If
Set objTxt = objFSO.OpenTextFile(txtFileName, 1)
strText = objTxt.ReadAll 'read the text file content
objTxt.Close
arrCond = Split(strText, vbCrLf) 'put it in an array splitting on vbCrLf (end of line)
N1 = Range("C" & Rows.Count).End(xlUp).Row 'last row of the G:G column
For d1 = 1 To N1 'iterate between the existing range
For Each El In arrCond 'check each element of the array keeping conditions
If El <> "" Then
If InStr(1, sh.Cells(d1, 3).Text, El, vbTextCompare) > 0 Then
If rngDel Is Nothing Then 'if the range to be deleted not Set
Set rngDel = sh.Cells(d1, 3)
Else
Set rngDel = Union(rngDel, sh.Cells(d1, 3)) 'if already Set
End If
End If
End If
Next El
Next d1
'delete all the rows at once:
If Not rngDel Is Nothing Then rngDel.EntireRow.Delete xlUp
End Sub
-------------------- c3delete.txt file with content first second ---------------- excel file a1 b2 c3 five first second seven first nine