Forum Discussion

minhhai91's avatar
minhhai91
Copper Contributor
Jun 25, 2021
Solved

Duplicate management with condition

Hello,

 

I would like to delete the duplicates (red ones) and keep those with the oldest date (green) with a macro on VBA.

 

 

Thank you in advance,

 

Frédéric

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jun 25, 2021

    minhhai91 

    Sort the range on column E, from oldest to newest.

    Click Remove Duplicates on the Data tab of the ribbon.

    Select only column A (ID), then click OK.

     

    As a macro:

    Sub RemoveDups()
    Application.ScreenUpdating = False
    With Range("A1").CurrentRegion
        .Sort Key1:=Range("E1"), Header:=xlYes
        .RemoveDuplicates Columns:=1, Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 25, 2021

    minhhai91 

    Sort the range on column E, from oldest to newest.

    Click Remove Duplicates on the Data tab of the ribbon.

    Select only column A (ID), then click OK.

     

    As a macro:

    Sub RemoveDups()
    Application.ScreenUpdating = False
    With Range("A1").CurrentRegion
        .Sort Key1:=Range("E1"), Header:=xlYes
        .RemoveDuplicates Columns:=1, Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub

Resources