Remove Duplicates?

This was recorded with vba recorder.  How come it does not remove duplicate rows?  It needs to be dynamic because the number of rows chg in the active sheet.  For some reason it is not removing them all.  After I run the code Row2 and Row76 are the same?  One should be gone.  Any Ideas?  It happens to when I select column A and expand to column U

    Cells.Select
    Range("A62").Activate
    ActiveSheet.Range("$A$1:$U$486").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Header:=xlYes

Open in new window

RemoveDupsExample.xlsx
RWayneHAsked:
Who is Participating?
 
Evan CutlerVolunteer Chief Information OfficerCommented:
Sorry, Typo

    Columns("A:U").Select
    ActiveSheet.Range("$A:$U").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Header:=xlYes

THere you go.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
Just for silliness stake:

In the line:
Range("A62").Activate

have you tried: Range("$A$1:$U$486").Activate

My thought here is that it's de-duplicating the Range A62.
0
 
RWayneHAuthor Commented:
Nope, did not work... plus need to select range dynamically...  number of rows chg...
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Evan CutlerVolunteer Chief Information OfficerCommented:
This seems to work for me.

Columns("A:U").Select
    ActiveSheet.Range("$A$1:$U$486").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Header:=xlYes

Now, keep in mind, if one value is different, then it's not a duplicate (as the code shows).
0
 
RWayneHAuthor Commented:
Nope, that did not work either...  please review the attached file... that I am running it on...  perhaps it is something with the data ?  There is not really a key field... it is a combination of fields.
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
ok.  I figured it out.  You have issues with the format of your excel.
While I'm not educated on whether or not macro-run removeduplicates calls use formatting or other stuff, you have something in your excel file that is causing the rows to appear different.

Save your XLS as a CSV and then RE-open in Excel.  From there, attempt to removeduplicates.  Keep in mind CSV does not save macros, so you will have to set it manually when opening.  This is your control test.

To fix the XLS, check to make sure there's no hidden characters or anything added when generating the file.
0
 
RWayneHAuthor Commented:
I figured out a couple things.  One is that there are a ton of empty spaces in the table, I removed spaces in a number of columns...  however when I looked at the date in column U it has the time in it.  I am using =now().  Is there something else like =date() that I can use?  Trying that now.  Will let you know.
0
 
RWayneHAuthor Commented:
Ok now that data is good, it works when I run it manually, but not in the macro?  How do I get this to work within the macro?
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
in Excel, go to the Developer tab.
If it's not visible, add it in the Ribbon control.

In the Macros section, look for "Record Macro"
Do the work in excel, then "Stop Macro"

You will see your new macro there.
Remember, after you do this, you MUST re-save the document in XLSX format.  CSV will not take it with the data.
0
 
RWayneHAuthor Commented:
I am confused... I know how to record a macro but what do I need to do?
Move a copy of the sheet out to .csv   run the remove dups and import it back in?  Is there not some other type of vba that could be thrown at it to eliminate having to do the import/export steps?
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
So, what you could do, is record your actions of "cleansing" the data, and add that to the VBA.

Your original code should have worked, so it was the matter of the data.  If you can cleanse it before de-duplication, you should get the result  you are looking for.

If you can't, then you need to go back to where you got the data, and ask that party to send you a cleaner output.
0
 
RWayneHAuthor Commented:
Ok. I added the cleansing code for the data...  it is good, however it skips over this code.

Columns("A:U").Select
ActiveSheet.Range("$A:$U").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
         7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Header:=xlYes
    

Open in new window


When done, I have to do it manually and it works.   ??  How do you get it to work within vba?
0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
are both pieces of code in the same function?
can you please paste it here?
0
 
RWayneHAuthor Commented:
Are you talking about the cleansing code?

Range("V3").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-1])&""/""&DAY(RC[-1])&""/""&YEAR(RC[-1])"
Set MyRange = Range(Range("V3"), Range("V" & Range("A2").End(xlDown).Row))
Range("V3").AutoFill Destination:=MyRange
Calculate
MyRange.Copy
MyRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Columns("U:U").Select
Selection.Delete Shift:=xlToLeft
Range("U2").Select
ActiveCell.FormulaR1C1 = "Today"
Range("U3").Select

Range("A:F,H:P,R:T").Select
Range("R1").Activate
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Columns("A:U").Select
ActiveSheet.Range("$A:$U").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
         7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21), Header:=xlYes

Open in new window

0
 
Evan CutlerVolunteer Chief Information OfficerCommented:
THis ran.
I included your code in a test function, then ran it from the macros menu.
Attached is the result:

RemoveDupsExample--1-.xlsx
0
 
RWayneHAuthor Commented:
Helps if I was on the proper sheet tab!!!!  urgh..   Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.