I'm trying to create an excel macro that can delete duplicate rows and their original ones based on more than one column.

In the table below you will find the example data and the output for which I'm looking. I need to delete all duplicate rows but only look at some columns like in the example the first 3 headers.

Input table

Output table after executing the macro

I have tried to create a macro, but that only deletes the duplicate values and the original one stays. And I need to delete original one as well.

Sub RemoveDuplicatesMultipleColumns() Dim dataRange As Range Set dataRange = Range("A1:D8") dataRange.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes End Sub

CodePudding user response：

Probably, we can create a dummy variable first & calculate the frequency of first 3 columns using countifs, later filter & delete all rows whose count is >1... Not sure if their is any direct way though... As you mentioned inbuilt procedure of VBA's RemoveDuplicates can't get rid of original rows too... So maybe try this code then;

Sub delete_dup_rows()

Columns(5).ClearContents
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("E2").Formula = "=COUNTIFS($A:$A,$A2,$B:$B,$B2,$C:$C,$C2)"
Range("E2").Copy
Range("E2:E" & lRow).Select
ActiveSheet.Paste
Range("A1").Select

Set Rng = Range("A1").CurrentRegion
Rng.AutoFilter Field:=5, Criteria1:=">1"
Rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Rng.AutoFilter

Columns(5).ClearContents
End Sub

This is the output of your input data;

Hope this Helps...