Macro to filter Table to look for duplicates and delete older Entries

[Luke1690]

Hi All,

Happy New Year.

Table1 Range A4:AD4 including headers

im looking for a script that will filter my table (Table1) by column D (Header is D4) This is a unique number, to look for duplicates

Then in column F (Header is F4) is the date that data entry was entered in this format DD/MM/YYYY

I want to delete all duplicate old entries. Leaving only the latest input

could this also run when workbook is opened?

This way when the next data set is added the duplicates have already been deleted.

Thanks in advance

 

[HighAndWilder]

Hi All,

Happy New Year.

Table1 Range A4:AD4 including headers

im looking for a script that will filter my table (Table1) by column D (Header is D4) This is a unique number, to look for duplicates

Then in column F (Header is F4) is the date that data entry was entered in this format DD/MM/YYYY

I want to delete all duplicate old entries. Leaving only the latest input

could this also run when workbook is opened?

This way when the next data set is added the duplicates have already been deleted.

Thanks in advance

Run this on a COPY of your data.

Change the reference to Sheet1 as appropriate.

The table needs to be sorted as the Remove Duplicates line removes all but the last row for each value in column 4.

Public Sub subDeleteDuplicates()

    With Sheets("Sheet1").ListObjects("Table1")
    
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending
            .SortFields.Add Key:=Columns(6), SortOn:=xlSortOnValues, Order:=xlAscending
            .Header = xlYes
            .Apply
        End With
           
        .Range.RemoveDuplicates Columns:=Array(4), Header:=xlYes
    
    End With
    
End Sub

 

[HighAndWilder]

Hi All,

Happy New Year.

Table1 Range A4:AD4 including headers

im looking for a script that will filter my table (Table1) by column D (Header is D4) This is a unique number, to look for duplicates

Then in column F (Header is F4) is the date that data entry was entered in this format DD/MM/YYYY

I want to delete all duplicate old entries. Leaving only the latest input

could this also run when workbook is opened?

This way when the next data set is added the duplicates have already been deleted.

Thanks in advance

Please ignore Post 2 and use this code.

Public Sub subDeleteDuplicates()

    With Sheets("Sheet1").ListObjects("Table1")
    
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending
            .SortFields.Add Key:=Columns(6), SortOn:=xlSortOnValues, Order:=xlDescending
            .Header = xlYes
            .Apply
        End With
           
        .Range.RemoveDuplicates Columns:=Array(4), Header:=xlYes
    
    End With
    
End Sub

 

[Luke1690]

Legend.

Works perfectly.

Thanks for the speedy response.