Deleting duplicate rows

[vanessa h]

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?

[Bob Phillips]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[broro183]

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...

[DaveO]

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
>
>

[vanessa h]

Thanks all... It helped me..