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
– user2242044, Commented May 5, 2014 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
– Dmitry Pavliv, Commented May 5, 2014 at 21:20
@simoco I've made your suggested changes and am getting v as an empty array. updated code above. — user2242044
– user2242044, Commented May 5, 2014 at 21:31
You never add anything to v. Perhaps you mean v = d.Keys(). — David Zemens
– David Zemens, Commented May 5, 2014 at 21:42
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: stackoverflow.com/questions/11870095/… — Trace
– Trace, Commented May 5, 2014 at 22:26

David Zemens · Accepted Answer · 2014-05-06 01:41:31Z

2

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

edited May 6, 2014 at 1:41

answered May 5, 2014 at 21:19

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2242044 Over a year ago

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?

David Zemens Over a year ago

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.

brettdj · Accepted Answer · 2014-05-06 01:06:56Z

1

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

answered May 6, 2014 at 1:06

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Collectives™ on Stack Overflow

Deleting duplicates in a VBA Array

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related