Macro needed - Delete duplicate rows in table when certain cells contain values

Question

I have 4 sheets&nbsp;L010, L011, L020, L021 each containing 1 table named T_L010, T_L011, T_L020, T_L021 respectively, that ranges from A:R.Cols A:H contain the raw data and I:R are the ones that are filled by me.When raw data is sent to be added, it often contains assignments that have already been addressed previously.Columns B and E contain the most unique data (Job Assignments) to have as a reference to check against.I need a macro that deletes all duplicate rows based on columns I:R. e.g.:JA-22-419047 is found in&nbsp;B2&amp;B11 and CP19862 in&nbsp;E2&amp;E11.Delete row 11 because I11:R11 are empty (or perhaps better, because I2:R2 contain data).Sample data with one table T_L010.I would want1. the macro to be triggered manually and not automatically.2. to put the code individually in every sheet, so the code should contain only one table per sheet&nbsp;Thank you.

hansvogelaar · Accepted Answer

JoAvg&nbsp;
You don't really need a macro for that. you can use Data &gt; Remove Duplicates. If the duplicate rows with empty cells in columns I etc. are at the bottom, they will be removed.
As a macro:
Sub RemoveDups()
    Range("A1").CurrentRegion.Sort Key1:=Range("I1"), Header:=xlYes
    Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(2, 5), Header:=xlYes
End Sub

Forum Discussion

Macro needed - Delete duplicate rows in table when certain cells contain values

Share

Resources