Previous Topic Next Topic

Macro to delete duplicate items -Help please

Solved/Closed
Carlos - Aug 11, 2010 at 10:41 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 20, 2010 at 08:05 AM
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 responses

Answer 1 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 17, 2010 at 10:41 PM
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
0
Answer 2 / 4
carlos
Aug 18, 2010 at 04:14 PM
This is the link with a sample for better undertanding.

Thank you so much rizvisa1!!!

https://authentification.site/files/23862781/USAcx.xls
0
Answer 3 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 19, 2010 at 05:53 AM 
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
0
Answer 4 / 4
carlos
Aug 19, 2010 at 08:50 AM
rizvisa1,

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

Thank you for your time and patience.

Carlos
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 20, 2010 at 08:05 AM
Glad it worked out for you.
0