I have one excel sheet. In the first column I have some numbers which
are key numbers. I want to write a macro by which I want to delete the
rows which have duplicate data in that column.
for eg. these are the values of the column 1.

1
1
1
2
2
3
3
4
5

In this case I want to delete the first two rows of the column
containing 1. But I want to keep the last row containing 1 as it is.
Then again delete 1 row for 2 and keep the last row containing 2 as it
is, delete 1 row for 3 and keep the last row containing 3 as it is. In
short I want to keep only one row of a number. So finally my column
should be

1
2
3
4
5

Can anyone help me out for this?


--
vanessa h
------------------------------------------------------------------------
vanessa h's Profile: http://www.excelforum.com/member.php...o&userid=30731
View this thread: http://www.excelforum.com/showthread...hreadid=503975

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Application.CountIf(Range("A" & i & ":A" & iLastRow), Cells(i,
"A")) 1 Then
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
End If
Next i

If Not rng Is Nothing Then rng.Delete

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"vanessa h" wrote
in message ...

I have one excel sheet. In the first column I have some numbers which
are key numbers. I want to write a macro by which I want to delete the
rows which have duplicate data in that column.
for eg. these are the values of the column 1.

1
1
1
2
2
3
3
4
5

In this case I want to delete the first two rows of the column
containing 1. But I want to keep the last row containing 1 as it is.
Then again delete 1 row for 2 and keep the last row containing 2 as it
is, delete 1 row for 3 and keep the last row containing 3 as it is. In
short I want to keep only one row of a number. So finally my column
should be

1
2
3
4
5

Can anyone help me out for this?


--
vanessa h
------------------------------------------------------------------------
vanessa h's Profile:
http://www.excelforum.com/member.php...o&userid=30731
View this thread: http://www.excelforum.com/showthread...hreadid=503975

Hi Vanessa,
There maybe something useful for you on Chip Pearson's site, check
out:

http://www.cpearson.com/excel/deleting.htm &
http://www.cpearson.com/excel/duplicat.htm

*Other options are also listed in the thread:
http://www.excelforum.com/archive/in.../t-333766.html

hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=503975

I'd try something like this....

***Assumes that column A is where the duplicataes will appear and assumes
you want to compare row 2 to row 1 initially.***

Sub DeleteRows()

Dim intCurrentRow As Integer
Dim intLastRow As Integer

intCurrentRow = 2

Do While Len(Range("A" & intCurrentRow).text) 0

intLastRow = intCurrentRow - 1

If Range("A" & intCurrentRow).text = Range("A" & intLastRow).text Then

Rows(intLastRow & ":" & intLastRow).Delete Shift:=xlUp

End If

intCurrentRow = intCurrentRow + 1

Loop

End Sub

HTH.

"vanessa h" wrote:


I have one excel sheet. In the first column I have some numbers which
are key numbers. I want to write a macro by which I want to delete the
rows which have duplicate data in that column.
for eg. these are the values of the column 1.

1
1
1
2
2
3
3
4
5

In this case I want to delete the first two rows of the column
containing 1. But I want to keep the last row containing 1 as it is.
Then again delete 1 row for 2 and keep the last row containing 2 as it
is, delete 1 row for 3 and keep the last row containing 3 as it is. In
short I want to keep only one row of a number. So finally my column
should be

1
2
3
4
5

Can anyone help me out for this?


--
vanessa h
------------------------------------------------------------------------
vanessa h's Profile: http://www.excelforum.com/member.php...o&userid=30731
View this thread: http://www.excelforum.com/showthread...hreadid=503975

Thanks all... It helped me..


--
vanessa h
------------------------------------------------------------------------
vanessa h's Profile: http://www.excelforum.com/member.php...o&userid=30731
View this thread: http://www.excelforum.com/showthread...hreadid=503975