VBA Delete Both Duplicates Entire Row

[Coater]

I need a VBA code to delete all duplicates numbers and only leave what is not a duplicate number.

An example is 1, 2, 3, 4, 5, 5, 4, 3. The result will be 1, 2

 

[Coater]

Thanks you! The additional information now clarifies the issues raised.


In terms of Excel worksheets, that is a very small number.

Try this with a copy of your workbook.
Set the column that has the (possibly) duplicate numbers in the 'Const' line near the start of the code.

Sub DeleteDupeRows()
  Dim rCrit As Range
  Dim LastRow As Long

  Const DupesCol As String = "I"  '<- Column that your numbers of interest are in

  Application.ScreenUpdating = False
  LastRow = Range(DupesCol & Rows.Count).End(xlUp).Row
  Set rCrit = Range("ZZ1:ZZ2")
  rCrit.Cells(2).Formula = Replace("=COUNTIF(#$2:#$" & LastRow & ",#2)>1", "#", DupesCol)
  With Range(Replace("#1:#" & LastRow, "#", DupesCol))
    .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    .Offset(1).SpecialCells(xlVisible).EntireRow.Delete
  End With
  If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
  rCrit.ClearContents
  Application.ScreenUpdating = True
End Sub

Hello, I ran this code on 8,731 rows of numbers. The final result was 2,844. The correct number of rows should have deleted 471 resulting in a final 8,260 rows of unique numbers. The formula works fine with a 100 rows of numbers. Do I need to change something. Thanks again for all of your help.

 

[Coater]

Hello,

I verified the program results by hand and with numerous other data numbers. All were correct. Thank you once again.

 

[Peter_SSs]

Glad it seems to be resolved.

Are you saying that the code from post #10 did work or that you found another way?