Remove Duplicates

[shantraj.antin@gmail.com]

Hi All,

I was using the below code to remove duplicate rows.

In the current scenario I have couple of cells which are merged in Column A. If I run the macro I am losing the non duplicate values.

Kindly help me to revisit this code so that my Column A merged cells remain same and one of the duplicate rows gets deleted.

Please find the attached sample file.

 Sub Remove_Duplicates()
 
    Dim LastRowcheck As Long, n1 As Long
    Dim DelRange As Range
 
    With Worksheets("JobDetails")
        LastRowcheck = .Range("B" & .Rows.Count).End(xlUp).Row
 
        For n1 = 1 To LastRowcheck
            If .Cells(n1, 1).Value = Cells(n1 + 1, 1).Value Then
                If DelRange Is Nothing Then
                    Set DelRange = .Rows(n1)
                Else
                    Set DelRange = Union(DelRange, .Rows(n1))
                End If
            End If
        Next n1
 
        If Not DelRange Is Nothing Then DelRange.Delete
    End With
End Sub

 

[NARAYANK991]

Hi Shantraj ,

I think the issue is not merged cells , but the logic of how you are detecting duplicates.

Can you say what are the rows which contain duplicates ?

In my opinion , the duplicate rows are : 2 & 3 , 6 & 7 , 8 & 9 , 10 & 11 & 13.

So if the duplicate rows are to be deleted , we should delete rows 13 , 11 , 9 , 7 and 3 in this order.

Your posted code is not doing this , and this has nothing to do with merged cells.

Narayan

 

[shantraj.antin@gmail.com]

Hi Shantraj ,

I think the issue is not merged cells , but the logic of how you are detecting duplicates.

Can you say what are the rows which contain duplicates ?

In my opinion , the duplicate rows are : 2 & 3 , 6 & 7 , 8 & 9 , 10 & 11 & 13.

So if the duplicate rows are to be deleted , we should delete rows 13 , 11 , 9 , 7 and 3 in this order.

Your posted code is not doing this , and this has nothing to do with merged cells.

Narayan

 

[shantraj.antin@gmail.com]

 

[shantraj.antin@gmail.com]

Hi Narayan,

Thanks for your response.

Can you please help me to get the desired result to remove duplicate rows and retain single row.

Regards,
Shanthraj

 

[NARAYANK991]

Hi Shanthraj ,

See this file.

Narayan

 

[shantraj.antin@gmail.com]

Hi Narayan,

Thanks a lot. Its working fine.

Regards,
Shanthraj