Forum Discussion

JoAvg's avatar
JoAvg
Brass Contributor
Nov 28, 2022
Solved

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

I have 4 sheets 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 fi...
excel
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Nov 29, 2022

    JoAvg 

    You don't really need a macro for that. you can use Data > 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
JoAvg's avatar
JoAvg
Brass Contributor
Nov 29, 2022

Bumping this up for HansVogelaar would definitely need your help on this one.

HansVogelaar's avatar
HansVogelaar
MVP
Nov 29, 2022

JoAvg 

You don't really need a macro for that. you can use Data > 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

Resources