Deleting Repititions

[Guest]

Hi,
I have a list of 100 values in Column B, I need to delete repetitions,
the following code needs tweaking, help is much appreciated

Sub del()

Dim i, j
I<>J
For i = 1 To 10
For j = 1 To 10

If Cells(i, 2) = Cells(j, 2) Then
Cells(i, 2).EntireRow.Delete

End If

Next
Next
End Sub

 

[Otto Moehrbach]

This is untested.
Sub DupeClear()
Dim r As Long
Dim c As Long
Dim x as Variant
Dim y as Variant
r = 1
c = 2
Do Until Cells(r, c).Value = ""
x = Cells(r, c).Value
If x = y Then
Rows(r).EntireRow.Delete
Else
r = r + 1
End If
y = x
Loop
End Sub

HTH Otto

 

[Rob van Gelder]

Sub test()
Const cCol = 2
Dim i As Long, j As Long, rng As Range

i = 1: j = 100
Do Until i > j
Set rng = Cells(i, cCol)
If WorksheetFunction.CountIf(Range(rng, Cells(j, cCol)), rng) > 1
Then
rng.EntireRow.Delete xlShiftUp
j = j - 1
Else
i = i + 1
End If
Loop
End Sub

 

[Claud Balls]

How about this?
Range("A1:A100").AdvancedFilter _ Action:=xlFilterInPlace,
Unique:=True

 

[alondon]

Folks,

After seeing Rob van Gelder's solution, my solution looks rather "quaint"

Cheers,

A. London

____________________________________________________
Sub DelDup()
'Eliminate Rows with duplicate entries in Col A and sort

Dim I, J, theRows As Integer
Dim TestValue As String
Dim theList As Range

'Initialize
theRows = 50 'Number of Rows to eliminate Dups
Set theList = Range("A1:A" & theRows) 'Column A

'Eliminate Duplicates
For I = 1 To theList.Count
TestValue = theList.Cells(I, 1)
For J = I + 1 To theList.Count
If TestValue = theList.Cells(J, 1) Then
Rows(J).Delete
J = J - 1
End If
Next J
Next I

'Cleanup
Columns("A:AZ").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Range("A1").Select

End Sub