Solved

Remove Duplicates?

Posted on 2016-11-09
16
26 Views
Last Modified: 2016-11-09
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
0
Comment
Question by:RWayneH
  • 8
  • 8
16 Comments
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41880831
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
 

Author Comment

by:RWayneH
ID: 41880865
Nope, did not work... plus need to select range dynamically...  number of rows chg...
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41880872
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
 
LVL 9

Accepted Solution

by:
Evan Cutler earned 500 total points
ID: 41880874
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
 

Author Comment

by:RWayneH
ID: 41880882
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41880903
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
 

Author Comment

by:RWayneH
ID: 41880921
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
 

Author Comment

by:RWayneH
ID: 41880957
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41880963
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
 

Author Comment

by:RWayneH
ID: 41880971
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41880973
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
 

Author Comment

by:RWayneH
ID: 41880982
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41881016
are both pieces of code in the same function?
can you please paste it here?
0
 

Author Comment

by:RWayneH
ID: 41881029
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 41881066
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
 

Author Closing Comment

by:RWayneH
ID: 41881092
Helps if I was on the proper sheet tab!!!!  urgh..   Thanks for the help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now