Deleting Repititions
 

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

Deleting Repititions
 

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
"teresa" wrote in message
...
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

Deleting Repititions
 

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"teresa" wrote in message
...
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

Deleting Repititions
 

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deleting Repititions
 

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

"teresa" wrote in message
...
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