Problem solved

[rachaelb]

Dear All

This is my first post! Hope someone can help.

I would like to know how to automatically delete rows if a particular cell in that row is a duplicate.

My 'A' column has a number of duplicates in it. If the cells in this column are duplicates I need to delete the whole row corresponding to that cell. Only this particular cell is a duplicate in the row - the other cells may not be duplicates.

Hope this makes sense - I would be really greatful for any help

Rachael



[ This Message was edited by: rachaelb on 2002-05-08 08:46 ]

[Tom Schreiner]

Hi
Insert this code into a standard module.
1. Hit Alt-F11
2. In VBA editor hit Alt-I (Insert Menu)
3. Hit M (Insert Module)
4. Paste this code as is
5. Close VBA editor

If you do not know how to run a macro,
do the following from your spreadsheet
which has the duplicates:

Hi Alt-F8 and 2X-Click on "DeleteRows"
to run the procedure.

If you have any problems, repost.

Tom

Sub DeleteRows()

Dim ColA_uRng, Cntr

For Cntr = 1 To ActiveSheet.UsedRange.Columns(1).Rows.Count

    Set ColA_uRng = Range("A" & Cntr & ":A" & ActiveSheet. _

      UsedRange.Columns(1).Rows.Count)

    If Application.WorksheetFunction.CountIf(ColA_uRng, _

      Range("A" & Cntr).Value) > 1 Then

      Rows(Cntr).Delete

      Cntr = Cntr - 1

    End If

Next

End Sub

[Chris Davison]

Better still, edit your topic(intitial post), by tagging on a word or phrase such as, "Problem Solved", or "Resolved".

Excellent idea.....good thinking Tom

[Brian]

If you would prefer a way to simply mark the duplicate cells so that you can delete them manually:
Assuming the Duplicates to test are in col A

Enter this in cell A1 and the drag (fill) down as far as you have data:

=countif($a$1:$a1,a1)

This will display "1" for the first occurence, "2" for the second, etc.

Manually delete any row with a number greater than 1.

You can the autofilter the list for all numbers >1 and delete them manually