Link to home
Start Free TrialLog in
Avatar of RWayneH
RWayneHFlag for United States of America

asked on

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
Avatar of Evan Cutler
Evan Cutler
Flag of United States of America image

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.
Avatar of RWayneH

ASKER

Nope, did not work... plus need to select range dynamically...  number of rows chg...
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).
ASKER CERTIFIED SOLUTION
Avatar of Evan Cutler
Evan Cutler
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RWayneH

ASKER

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.
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.
Avatar of RWayneH

ASKER

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.
Avatar of RWayneH

ASKER

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?
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.
Avatar of RWayneH

ASKER

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?
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.
Avatar of RWayneH

ASKER

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?
are both pieces of code in the same function?
can you please paste it here?
Avatar of RWayneH

ASKER

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

THis ran.
I included your code in a test function, then ran it from the macros menu.
Attached is the result:

RemoveDupsExample--1-.xlsx
Avatar of RWayneH

ASKER

Helps if I was on the proper sheet tab!!!!  urgh..   Thanks for the help.