Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Macro to delete duplicate items -Help please

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,


I'm trying to create a macro to delete the row that has a word at the biginnig and the same word at the end. Example: MIA/SFO/MIA
There is a "/" between words or acronyms in each row

Any help?

Thanks a million!!!

Carlos

4 replies

Answer 1 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Answer 2 / 4

This is the link with a sample for better undertanding.

Thank you so much rizvisa1!!!

https://authentification.site/files/23862781/USAcx.xls
Answer 3 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768 
Sub removedup()
Dim lLastRow As Long
Dim vLPos As Variant
Dim vRPos As Variant

    Application.ScreenUpdating = False
    lLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Do While lLastRow > 0
        
        vLPos = InStr(1, Cells(lLastRow, "A"), "/")
        If vLPos > 0 Then
            vRPos = InStrRev(Cells(lLastRow, "A"), "/")
            
            If ((vRPos > 0) And (vRPos > vLPos)) Then
                
                If (Left(Cells(lLastRow, "A"), vLPos - 1) = Mid(Cells(lLastRow, "A"), vRPos + 1)) Then
                    
                    Cells(lLastRow, "A").Delete shift:=xlUp
                End If
            End If
        
        
        End If
    
        lLastRow = lLastRow - 1
    Loop
    
    Application.ScreenUpdating = True
End Sub
Answer 4 / 4

rizvisa1,

This macro works great!!! It's so useful to me.

Thank you for your time and patience.

Carlos
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Glad it worked out for you.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!