Deleting duplicates in a VBA Array

Question

CODE WORKS CORRECTLY. MODIFIED BASED ON HELP FROM RESPONSES.

I have the following code to remove duplicates from a array, MyArray. The code gets a debugging error at: d(MyArray(i)) = 1. The error is subscript out of range. Not sure what is causing this and what is wrong with my code.

Sub DataStats1()
Dim Range1 As Range
Dim MyArray As Variant

Set Range1 = Application.InputBox("Select Range1:", Title:="Set Data Range", Type:=8)

Range1.Select
MyArray = Application.Transpose(Application.Transpose(Range1.Value))



Dim d As Object
Set d = CreateObject("Scripting.Dictionary")

For Each el In MyArray
   d(el) = 1
Next

Dim v As Variant
v = d.Keys()


For i = 1 To UBound(v)
MsgBox v(i)
Next i
End Sub

@simoco thanks I didn't realize I could do that. I thought I couldn't select an answer because of my low reputation. I just went back and selected answers for many of them. — user2242044, May 05 '14 at 21:17
about your Q: `MyArray` is always 2D array, even if you select single row/column range. E.g. for single column range you should use `d(MyArray(i,1)) = 1` instead `d(MyArray(i)) = 1`. But I suggets you to change `For i = LBound(MyArray) To UBound(MyArray)` to say `For Each el in MyArray` and then `d(el) = 1` — Dmitry Pavliv, May 05 '14 at 21:20
@simoco I've made your suggested changes and am getting v as an empty array. updated code above. — user2242044, May 05 '14 at 21:31
You never add anything to `v`. Perhaps you mean `v = d.Keys()`. — David Zemens, May 05 '14 at 21:42
David you are correct but this still results in an empty array for v — user2242044, May 05 '14 at 22:01
The easiest would be to `debug.print(MyArray(i))` to see when the error occurs. Note that a dictionary is not supposed to have duplicate keys. — html_programmer, May 05 '14 at 22:16
@Kim Gysen why use i with MyArray? That variable is not used until array V which I know is blank. — user2242044, May 05 '14 at 22:21
I was looking at your initial code (second line). Anyhow, check your locals window and use debug.print to debug your code. Check the content of your dictionary for instance. Then assign the values from your dictionary to your array. Not sure if this is the most obvious way though. If you want to remove duplicates from an array, check for example: http://stackoverflow.com/questions/11870095/vba-remove-duplicates-from-array — html_programmer, May 05 '14 at 22:26
@user2242044 no, you are mistaken. If you do `v = d.Keys()` after you have assigned to the dictionary (instead of `For each v in d.Keys()`) and examine the contents of `v` in the Locals window, you should see that it is not an empty array: http://imgur.com/HUlYER4 — David Zemens, May 06 '14 at 01:35

David Zemens · Accepted Answer · 2014-05-06T01:41:31.650

You should learn to stop relying on Selection (this is after all why you have declared your variables...). You can do MyArray = Range1.Value instead.

Now, a Range Array is always going to be 2-dimensional, you instead of that, you will acutally need to do this if you are selecting a COLUMN range:

MyArray = Application.Transpose(Range1.Value)

Or this, if you are selecting a ROW range:

MyArray = Application.Transpose(Application.Transpose(Range1.Value)

You may need to do other operations if it is multi-dimensional range. I haven't tested.

Here are some ideas:

Sub DataStats1()
Dim Range1 As Range
Dim MyArray As Variant
Dim v As Variant
Dim d As Object

Set Range1 = Application.InputBox("Select Range1:", Title:="Set Data Range", Type:=8)

MyArray = Application.Transpose(Application.Transpose(Range1.Value))

Set d = CreateObject("Scripting.Dictionary")

For Each el In MyArray
   d(el) = 1
Next

'## Assign the Keys to an array:
v = d.Keys

'## At this point, v is an array of unique values.
'   Do whatever you want with it:
'
'Print the list to a COLUMN new sheet:
Sheets.Add
Range("A1").Resize(UBound(v) + 1).Value = Application.Transpose(v)


'Or print the list to a msgBox:
MsgBox Join(v, ", ")

'Or print to the console:
Debug.Print Join(v, ", ")

End Sub

Why do you have to transpose both ranges. My understanding is a array is 2D with a width of one and a height of any number. Shouldn't you only need to tranpose a column range? — user2242044, May 05 '14 at 21:35
Range arrays area always 2-dimensional and that is why you have to transpose them to get a one-dimensional array (if you don't believe me, try it out for yourself :)). Alternatively, you can work with the multi-dimensional arrays. As for **why?** I don't know why, that's just how it is. Probably to mimic the `Cells(_row_, _column_)` construct of the Range object. It would be confusing if `MyRange.Cells(1,1)` was not the same as `MyArray(1,1)`. But I am not sure about that, it just seems like the likely reason to me. — David Zemens, May 05 '14 at 21:46

score 1 · Answer 2 · answered May 06 '14 at 01:06

Something like this (for a single column or single row given you use Transpose)

Sub DataStats1()
Dim Rng1 As Range
Dim MyArray As Variant
Dim MyArray2 As Variant
Dim el
Dim d As Object

On Error Resume Next
Set Rng1 = Application.InputBox("Select Range1:", Title:="Set Data Range", Type:=8)
On Error GoTo 0

If Rng1 Is Nothing Then Exit Sub

MyArray = Application.Transpose(Application.Transpose(Rng1.Value))
Set d = CreateObject("Scripting.Dictionary")

For Each el In MyArray
If Not d.exists(el) Then d.Add el, 1
Next

MyArray2 = d.items

End Sub

Deleting duplicates in a VBA Array

2 Answers2

Linked