Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating duplicates from a variant variable
When I apply your code below, I get the following error:
User-defined type not defined? How should this variable type be defined? Thanks. Patrick Lee wrote: One solution is to store your strings as keys for a Collection or Dictionary (to use the latter, set a reference in your VBA project to Microsoft Scripting Runtime) object. Keys for both of these objects have to be unique. Personally, I would use a Dictionary object dim dic1 as Dictionary Set dic1 = New Dictionary For iCounter = 1 to lYourNumberOfStrings dim yourstring as string ' retrieve yourstring depending on iCounter if not dic1.exists(yourstring) then ' we know that yourstring has not already been added as a key to the dictionary dic1.Add yourstring, 1 ' this adds yourstring as a key and 1 as the value referenced by this key (the value is not used here so anything will do) end if next iCounter ' after this loop, dic1.Keys() is an array going from 0 to dic1.Count -1 with the unique values of your strings hth Patrick -----Original Message----- I have a variant variable holding string values How shall eliminate the duplicate strings from this variable, retaining only one value. Eg: If i have the element "TOM" twice in that array, i wish to delete one. which is the most effective way to handle this? Thanks, Leo . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Eliminating duplicates from a variant variable
Hi Smokiibear,
Did you set a reference to the Microsoft Scripting Runtime library, as suggested by Patrick? If I failed to do this, I could reproduce your error. .. --- Regards, Norman "smokiibear" wrote in message ups.com... When I apply your code below, I get the following error: User-defined type not defined? How should this variable type be defined? Thanks. Patrick Lee wrote: One solution is to store your strings as keys for a Collection or Dictionary (to use the latter, set a reference in your VBA project to Microsoft Scripting Runtime) object. Keys for both of these objects have to be unique. Personally, I would use a Dictionary object dim dic1 as Dictionary Set dic1 = New Dictionary For iCounter = 1 to lYourNumberOfStrings dim yourstring as string ' retrieve yourstring depending on iCounter if not dic1.exists(yourstring) then ' we know that yourstring has not already been added as a key to the dictionary dic1.Add yourstring, 1 ' this adds yourstring as a key and 1 as the value referenced by this key (the value is not used here so anything will do) end if next iCounter ' after this loop, dic1.Keys() is an array going from 0 to dic1.Count -1 with the unique values of your strings hth Patrick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Eliminating duplicates | Excel Discussion (Misc queries) | |||
Eliminating Duplicates in Excel 2007 | Excel Worksheet Functions | |||
Need help eliminating text duplicates! | Excel Discussion (Misc queries) | |||
is it possible to combine data sets, eliminating duplicates? | Excel Worksheet Functions | |||
Eliminating Duplicates | Excel Programming |