Count duplicate rows then delete duplicates

Hi

See how this goes.

Sub aaa()
  Range("A1:C1").Insert shift:=xlDown
  Range("A1:C1").Value = Array("H1", "H2", "H3")
  lastrow = Cells(Rows.Count, 1).End(xlUp).Row
  
  Range("C2").Formula = "=sumproduct(--($A$2:$A$" & lastrow & "=A2),--($B$2:$B$" & lastrow & "=b2))"
  Range("C2").AutoFill Destination:=Range("C2:C" & lastrow)
  Range("C2:C" & lastrow).Value = Range("C2:C" & lastrow).Value
  
  Range("A1:C" & lastrow).AdvancedFilter xlFilterInPlace, , , True
  Range("A2:C" & lastrow).SpecialCells(xlCellTypeVisible).Copy Destination:=Cells(lastrow + 1, 1)
  ActiveSheet.ShowAllData
  Range("A1:C" & lastrow).Delete shift:=xlUp
End Sub

rylo

Thanks for this post. Great to see someone's response from 2009 helping me out today.

I was able to adapt this code for a dataset with more columns (7) by increasing the array size and range formulas, but now I have a similar situation with a dataset continues 50+ columns of data. Do you know of a way to reproduce this same output WITHOUT having to manually type out the coding for each new column (i.e. "=sumproduct(--($A$2:$A$" & lastrow & "=A2),--($B$2:$B$" & lastrow & "=b2))"?