Solved

Remove Duplicates?

Posted on 2016-11-09
16
20 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 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

18 Experts available now in Live!

Get 1:1 Help Now