How do we modify program to remove unique values in cell and do not end of string?

Viewed 63

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

0 Answers
Related