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?

 

[marionmccaleb]

Thanks for your suggestion Erik but since I'm not a programmer I think I'll stick with HalfAce's code. Thanks also HalfAce for the detailed instructions you gave me above for installing the code which I am studuying---one question---what is a 'workbook'? This terminology is new to me----I see Workbook when I open up a new file under the file menue which is what you'e referring to I believe---I'll study your in instructions and try a few things in order to master this process. How very nice of you to work with me---regards, Marion McCaleb

 

[HalfAce]

Hello Marion, (thanks for the kind words.)
You're right. A file is technically a 'workbook' - which will consist of one or more worksheets.

 

[marionmccaleb]

I've been studying your instructions HalfAce and think I understand them---I finally figured out a 'worksheet' was just the file containing the info I want to process as you point out---I made a dummy file to test out the code and will do so tomorrow when I'm fresh---best wishes, Marion

 

[HalfAce]

I finally figured out a 'worksheet' was just the file containing the info I want to process

You're almost right. The file is actually the workbook. (They mean the same thing but
vba views it as 'workbook'.)
When you have a workbook open, at the bottom left of your screen you should see some
'tabs' that are by default named "Sheet1", "Sheet2", "Sheet3", etc.
These are the worksheets contained within your workbook.
You can add sheets, delete them, rename them, etc.

Your code is written assuming sheet1 is the name of the sheet your data is on in your
workbook.

Let me know how it works when you get a chance to try it out. If it isn't doing what you
want we'll get it fixed right up.

 

[erik.van.geit]

Thanks for your suggestion Erik but since I'm not a programmer I think I'll stick ...

Marion,

from this post it's clear you are learning

but you don't have to be a progammer to
1. goto the link I posted
2. copy the code like you did with Halfaces code
3. delete only one quote before one line (& the line before it is clearly announced)

furthermore we can help it you get in trouble, like Halface so nicely did thusfar

you can stick to Halfaces code
there is one drawback: if there are many rows to check, it will run slow because it's using a loop (especially if there are many rows to delete)

a test

Option Explicit

Sub test()
Dim i As Long
Dim rng As Range

Set rng = Cells(1, 1)
    For i = 3 To 2000
    Set rng = Union(rng, Cells(i * 2, 1))
    Next i
    
MsgBox rng.Cells.Count
rng = 1
rng.Select

End Sub

kind regards,
Erik

 

[marionmccaleb]

HalfAce, I am following the steps you gave me for my trial run but have run into a compile problem---all 6 steps under "To install the code" in your instructions were no problem but when I get to the steps under "To run the code" I get an error message that states:

Complie error:
Expected end of statement

Also in step 2), the macro name 'DeleteDupesDemo' didn't show up so I just typed it in---maybe this is part of the problem---Marion

 

[HalfAce]

Hmm. . .
If the macro name didn't show up when you pressed F8 then the only thing I can suspect
is that perhaps it got pasted into a module in the wrong workbook.
Is that a possibility, or was your copied workbook the only file you had open at the time
you inserted the module?

As for the error statement, if you were able to run the code at all (which it sounds like
you were if you got the error), try it again and when the error occurs, click the 'Debug'
button and let us know which line of code (if any) is highlighted in yellow.

I can't seem to reproduce the same error so I don't know right off what the problem
may be.

 

[marionmccaleb]

The only Excel file open was my test file called 'testDB.xl---the line of code where the problem is reads:

'Operation Explicit Sub DeleteDupesDemo ()...' with the Sub highlighted to show where the problem is.

It's puzzling that the name of the Sub doesn't caome up and requires my typing it in---I'll play around some more while you ponder the information above---Marion

 

[HalfAce]

Oh, OK.
Assuming you mean "Option Explicit Sub DeleteDupesDemo ()" then just go into
your code module and click the curser just in front of the "S" in the word 'Sub' and press
Enter once or
twice. (Or you can backspace out the words 'Option Explicit' altogether.)
Either way you'll see the text go from red to black.
Option Explicit is just there to ensure all the variables have been defined with a specific data
type (which I did).

After doing that you should be able to continue on with the instructions to run the code.

 

[marionmccaleb]

I suspect my problem is related to the fact that the sub 'DeleteDupesDemo' doesn't appear in the MACRO panel (in the Marco Name block)---I'm probably overlooking something simple---I'm going to put this aside for today and have another look tomorrow---best wishes and thanks, Marion