Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repeating Values from an Array
Hello,
I have a macro that writes values from cells into an array. Is it possible to delete repetitive values from the array or create another array that would only have different values? Any help on how to accomplish the above would be greatly appreciated. Thank You! Magnivy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repeating Values from an Array
Magnivy wrote:
Hello, I have a macro that writes values from cells into an array. Is it possible to delete repetitive values from the array or create another array that would only have different values? Any help on how to accomplish the above would be greatly appreciated. Thank You! Magnivy If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook arr2=ArrayUniques(arr1) will do it. Use of the ArrayUniques will require that there be an effective reference to Microsoft Scripting Runtime. Alan Beban |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repeating Values from an Array
This is very helpful. Thank you very much Alan!
"Alan Beban" wrote: Magnivy wrote: Hello, I have a macro that writes values from cells into an array. Is it possible to delete repetitive values from the array or create another array that would only have different values? Any help on how to accomplish the above would be greatly appreciated. Thank You! Magnivy If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook arr2=ArrayUniques(arr1) will do it. Use of the ArrayUniques will require that there be an effective reference to Microsoft Scripting Runtime. Alan Beban |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repeating Values from an Array
Hey Alan,
I tried the ArrayUniques on a worksheet, and it only returned the first value of the InputArray. Maybe its because I didnt specify a reference to Microsoft Scripting Runtime? I'm not sure what that means. It would be great if you could elaborate on that. Thanks! Magnivy "Alan Beban" wrote: Magnivy wrote: Hello, I have a macro that writes values from cells into an array. Is it possible to delete repetitive values from the array or create another array that would only have different values? Any help on how to accomplish the above would be greatly appreciated. Thank You! Magnivy If the functions in the freely downloadable file at http://home.pacbell.net/beban are available to your workbook arr2=ArrayUniques(arr1) will do it. Use of the ArrayUniques will require that there be an effective reference to Microsoft Scripting Runtime. Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Repeating Values from an Array
This is a function that removes duplicates from a 1-D array.
Function RemoveDuplicates(arr1 As Variant, _ DoSort As Boolean) As Variant 'removes duplicates from a 1D-array '---------------------------------- Dim NoDupes As New Collection Dim i As Long Dim LB As Byte Dim arr2() LB = LBound(arr1) 'The next statement ignores the error caused 'by attempting to add a duplicate key to the collection. 'The duplicate is not added - which is just what we want! '-------------------------------------------------------- On Error Resume Next For i = LBound(arr1) To UBound(arr1) NoDupes.Add arr1(i), CStr(arr1(i)) 'Note: the 2nd argument (key) for 'the Add method must be a string '-------------------------------- Next i On Error GoTo 0 ReDim arr2(LB To NoDupes.Count - (1 - LB)) 'copy the collection to array '---------------------------- For i = 1 To NoDupes.Count arr2(i - (1 - LB)) = NoDupes(i) Next i RemoveDuplicates = arr2 End Function It would be easy to adjust the code to deal with 2-D arrays. RBS "Magnivy" wrote in message ... Hello, I have a macro that writes values from cells into an array. Is it possible to delete repetitive values from the array or create another array that would only have different values? Any help on how to accomplish the above would be greatly appreciated. Thank You! Magnivy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Last values in an array | Excel Programming | |||
Deleting blank values from an array | Excel Programming | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
Deleting blank values from an array | Excel Programming | |||
Deleting series of repeating rows | Excel Programming |