Deleting Duplicate Records

[marionmccaleb]

I have a long list of names some of which are duplicated once---I want to delete the records of both of these names (not just one which the Filter function will do). How can I do this?

 

[HalfAce]

Does the macro's name still not appear (when you press Alt+F8) after you've made
the change mentioned in my last post?

 

[marionmccaleb]

I put the cursor right before 'sub' and clicked twice and the red changed to black but when I pressed Alt+F8 it showed:

Option Explicit (in blue )

And then the rest of code apeared in red again below the above line (note: the code always appears as a single line but that shouldn't make any difference)

The above all appears in the panel titled "test DB.xls - Module 1 (Code)". there is no place for a name in this panel which has blocks at the top called (General) and (Declarations)

My worksheet is "test DB.xls"

 

[marionmccaleb]

I just now tried something new---I copied the code in the link Ann Troy presented on page 1---I then used the procedures outlined in your steps HalfAce and the program ran and deleted all duplicate names--it did not however, delete the records asociated with these names---Marion

 

[marionmccaleb]

HalfAce, your code works great!! The problem I've been having is traceable to the way I copied your code. I copied the code into Word for storage and when I did a cut/past from Word apparently it affected the structure of your code---when I copied directly from your code in this posting and followed your instructions everything worked just beautifully. Please accept my appolgies for my error and also my sincere thanks for bearing with me---warm regards, Marion

 

[marionmccaleb]

One more thing HalfAce---will you give me the steps for recording DeletDupesDemo so I don't have to cut/paste from your code in this posting, each time I want to make a run?---thanks, Marion

 

[HalfAce]

Good morning Marion,
Good job getting your problem sorted out. :wink:
I had looked real quick at the code in that link that Anne offered and knew it would only
delete the excess instances of the duplicate values and leave the originals, and besides,
it isn't all that efficient because it's actually selecting each cell in the column before performing
the test on its value. If there aren't many rows then it wouldn't make much difference in
speed but if you were looking at 10s of thousands of rows it would make a huge difference.
Best not to select objects in your code if you don't have to. (And we seldom ever really have to.)

As for storing your code for future applications there are a number of ways to do it
but probably the easiest to convey & understand is to simply do what you did originally,
copy it from here and paste it somewhere. (You just need to make sure it's a clean
copy/paste which is all the problem was the first time.)
When doing it this way I recommend just pasting it into a normal excel worksheet instead
of a Word doc. Word is the devil!
(Anne, - if you're still reading this thread, I'm just kidding!
I have your book Dreamboat on Word and use it regularly when I'm in Word.
Well written and very helpful for 'Word hacks' like me!)

I'm not sure I follow when you say you don't want to copy/paste the code every
time you want to make a run. Once you install it in a module in a workbook, (and
save) it's part of that workbook and can be run any time you want. You can slap a
button on your worksheet and assign the code to it to run with just a mouse click.
Or (if you like) I can show you how to install it so you never have to actually invoke
the code yourself, it'll just run every time there's a duplicate value entered into that columm.

Is that what you meant in your last post or am I misunderstanding?

 

[marionmccaleb]

Thanks for getting back to me so soon HalfAce---I'll study your suggestions on recording DeleteDupesDemo--pasting it onto a workbook sound simplest so I'll try that first---could you explain how I accesss a workboook or is this another way of saying paste it on the worksheet I'm going to run?---I also want to thank Anne and Erick for their suggestions which are very much appreciated---best wishes, Marion

 

[HalfAce]

could you explain how I accesss a workboook or is this another way of saying paste it on the worksheet I'm going to run?

I'm afraid I don't follow.
What is it exactly you want to paste into the workbook? The code itself, or a button to click
when you want the code to run?

 

[marionmccaleb]

I'm just looking for a simple way to store the code so that I can access it without copying your code in this posting every time I want to run---my motivation is that I'm doing a number of trial runs but don't want to save the results. I tried copying the code and pasting to a blnk Excel worksheet but when I copied from the worksheet and inserted into the module it showed red on some of the lines of code---don't know what I'm doing wrong---this would be the simplest way for my purposes if I can figure out what the problem is---do you have any ideas as to what I could be doing wrong?---Marion

 

[marionmccaleb]

I just stored your code in an Outlook Express message and when I copy the code from the OE message and paste it into the module everthing works just fine---this may be a clumsy way of doing it but it suits my purposes---Marion