Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Office Automation (http://www.xtremevbtalk.com/-net-office-automation/)
-   -   Removing Duplicates (http://www.xtremevbtalk.com/-net-office-automation/312236-removing-duplicates.html)

XJOutsider 11-28-2009 12:04 PM

Removing Duplicates
 
I am Tring to Make this work but I do not understand the Colunms as objects Part. Here is what I got

Code:

wbTemplateSAS.Sheets("Component Matrix").Activate()
        Dim MyRange2 As Excel.Range
        With wbTemplateSAS.Sheets("Component Matrix")
            MyRange2 = .Range("A2:AY2", .Range("A65536:AY65536").End(Excel.XlDirection.xlUp))
        End With
        MyRange2.RemoveDuplicates(Colunm:=(HELP HERE) ,XlYesNoGuess.xlYes)

This is what the VBA looks like
Code:

ActiveSheet.Range("$A$2:$AY$113").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, _
        6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16), Header:=xlNo
    Sheets("SAS").Select


Mike Rosenblum 11-29-2009 12:06 PM

Hi XJ,

First, you need to be aware that the Range.RemoveDuplicates method is only available for Excel 2007 and above. If you need your program to work on Excel 2003 and below, then you should create your own routine to do this, such as sorting the rows and then deleting any duplicates.

When using the Range.RemoveDuplicates method, the Columns parameter is optional, and consists of an array of column numbers that tell the method which columns are to be considered when looking for duplicate data. If this parameter is omitted, then all columns are compared in each row.

For example, you could use the following to have the RemoveDuplicates method compare the first four columns:

Code:

MyRange2.RemoveDuplicates(Columns:= Array(1, 2, 3, 4), XlYesNoGuess.xlYes)
(I think that one of the reasons you might have been struggling with the above because you mispelled "Columns" as "Colunm".)

I hope this helps!

Mike

XJOutsider 11-29-2009 07:11 PM

Thanks Mike for the Reply. The Office just went completely to Office 2007. So that part is cool. I struggled through this Most of the end.
The code below
Code:

MyRange2.RemoveDuplicates(Columns:= Array(1, 2, 3, 4), XlYesNoGuess.xlYes)
I could not get to work.
These are the errors I got
Error 1 'Array' is a type and cannot be used as an expression.
Error 2 Named argument expected. This is on the XlYesNoGuess.xlYes section.
This may not be pretty but this is what I got to work.
Code:

Dim MyRange2 As Excel.Range
        With wbTemplateSAS.Sheets("Component Matrix")
            MyRange2 = .Range("A2:AY2", .Range("A65536:AY65536").End(Excel.XlDirection.xlUp))
        End With
        MyRange2.Select()
        MyRange2.RemoveDuplicates((1), XlYesNoGuess.xlYes)

Please chime if there is a better way to write this code.

Mike Rosenblum 11-30-2009 07:00 AM

I'm sorry, I forgot which sub-forum I was in and gave you code that would work for VBA, not VB.NET. My bad, I really apologize.

Instead of VBA's Array function, you should create a new array of Object(), holding integers. It would seem more reasonable to pass it an array of Integer(), but VBA's Array function actually returns an array of Object() holding integers, and this is what the Excel.Range.RemoveDuplicates method expects for the Columns parameter.

So your Columns parameter could be passed in something like this:
Code:

Columns:= New Object() {1, 2, 3, 4}
The Header parameter is easier. You simply need to prefix your enum name and value with Excel, because this is the library in which it resides. So your Header parameter, assuming that your data range does have headers, could be passed in something like this:
Code:

Header:= Excel.XlYesNoGuess.xlYes
Putting this all together, you should be able to make your call like the following:
Code:

range.RemoveDuplicates( _
    Columns:=New Object() {1, 2, 3, 4}, _
    Header:=Excel.XlYesNoGuess.xlYes)

I hope this helps...

:),
Mike


All times are GMT -6. The time now is 04:47 AM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.