[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Remove Duplicates?

Posted on 2016-11-09
16
Medium Priority
?
63 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 9

Accepted Solution

by:
Evan Cutler earned 2000 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
 
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Implementing simple internal controls in the Microsoft Access application.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month11 days, 4 hours left to enroll

612 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