Deleting Repetitions

[Guest]

Hi, I have a list of 100 numbers which frequently repeat themselves,
I need to delete the rows with repetitions, leaving only unique entries ,
my code is missing a few lines, help is much appreciated

1
2
3
1
13
..
..
3
Sub del()

For i = 1 To 10
For j = 1 To 10

If Cells(i, 1) = Cells(j, 1) Then
Cells(i, 1).Rows.Delete
End If
Next
Next
End Sub

 

[Guest]

You first need to sort your data to line up dup's in consecutive rows.. After
that, I use the following sub....

sub deldup
r = 1 ' start row
while cells(r,1).value <> "" ' Loop until you run out of numbers
cv1 = cells(r,1).value ' cell value 1
cv2 = cells(r+1,1).value ' cell value 2 (one row down)
if cv1 = cv2 then ' check values for dup
rows(r+1).select ' select row to delete
selection.delete ' delete row
r = r - 1 ' decrement row counter
end if
r = r + 1 ' increment row counter
wend
end sub

If you wanted a "non-destructive" list of uniques, you could use a
pivot-table...

Hope this helps!

 

[Debra Dalgleish]

Instead of looping through the data, you could use an Advanced Filter to
extract the unique values. There are instructions in Excel's Help, and here:

http://www.contextures.com/xladvfilter01.html#FilterUR

 

[Bob Phillips]

Sub del
j = Cells(Rows.Count, 1).End(xlUp).Row
For i = j To 1 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & j), Cells(i, 1).Value) > 1 Then
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

You should sort your rows first to line up consecutive dups... You can then
use the following:

Sub deldup()
r = 1
While Cells(r, 1).Value <> "" ' loop til done
cv1 = Cells(r, 1).Value ' store first cell value
cv2 = Cells(r + 1, 1).Value ' store second cell value (next row)
If cv1 = cv2 Then ' compare values
Rows(r + 1).Select ' select row for deletion
Selection.Delete ' delete row
r = r - 1 ' decrement row counter
End If
r = r + 1 ' increment row counter
Wend
End Sub

If you want a "non-destructive" list of uniques, you could use a pivot
table...

Hope this helps!

 

[Guest]

Try this... not too clean but it should work

Sub deldup()
r = 1
While Cells(r, 1).Value <> ""
cv1 = Cells(r, 1).Value
cv2 = Cells(r + 1, 1).Value
If cv1 = cv2 Then
Rows(r + 1).Select
Selection.Delete
r = r - 1
End If
r = r + 1
Wend
End Sub