Problem solved

rachaelb

New Member
Joined
May 6, 2002
Messages
1
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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

<pre>

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

</pre>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top