Removing Duplicates
Removing Duplicates
Removing Duplicates
Removing Duplicates
Removing Duplicates
Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates
Removing Duplicates Removing Duplicates
Removing Duplicates
Go Back  Xtreme Visual Basic Talk > > > Removing Duplicates


Reply
 
Thread Tools Display Modes
  #1  
Old 11-28-2009, 11:04 AM
XJOutsider XJOutsider is offline
Regular
 
Join Date: Jun 2009
Posts: 66
Default 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
Reply With Quote
  #2  
Old 11-29-2009, 11:06 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

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
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 11-29-2009, 06:11 PM
XJOutsider XJOutsider is offline
Regular
 
Join Date: Jun 2009
Posts: 66
Default

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.
Reply With Quote
  #4  
Old 11-30-2009, 06:00 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

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
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Removing Duplicates
Removing Duplicates
Removing Duplicates Removing Duplicates
Removing Duplicates
Removing Duplicates
Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates Removing Duplicates
Removing Duplicates
Removing Duplicates
 
Removing Duplicates
Removing Duplicates
 
-->