Deleting Repetitions
 

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

Deleting Repetitions
 

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!


"teresa" wrote:

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

Deleting Repetitions
 

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 he

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



teresa wrote:
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



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Deleting Repetitions
 

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)


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

Deleting Repetitions
 

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!

"teresa" wrote:

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

Deleting Repetitions
 

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


"teresa" wrote:

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