删除重复在VBA数组 [英] Deleting duplicates in a VBA Array

问题描述

code正确的作品。此基础上改装的帮助响应。

我有以下的code从一个阵列，MYARRAY删除重复。在code获得在调试错误： D（MYARRAY（I））= 1 。该错误是下标越界。不知道是什么原因造成这样的，什么是错我的code。

 子DataStats1（）
昏暗范围1由于范围
昏暗MYARRAY为Variant设置范围1 = Application.InputBox（选择范围1：标题：=设置数据范围中输入：= 8）Range1.Select
MYARRAY = Application.Transpose（Application.Transpose（Range1.Value））昏暗的Ð作为对象
集合D =的CreateObject（的Scripting.Dictionary）对于每个EL在MYARRAY
   D（EL）= 1
下一个昏暗的V作为变
V =运行起来也（）
对于i = 1到UBound函数（V）
MSGBOX V（I）
接下来，我
结束小组
 

解决方案

您应该学会停止依靠选择（这毕竟是为什么您已声明的变量。 ..）。你可以做 = MYARRAY Range1.Value 代替。

现在，射程数组总是将是2维，你不是说，你实际上可以需要做这个，如果你选择的是列范围：

= MYARRAY Application.Transpose（Range1.Value）

或者这样，如果你选择的是行范围：

= MYARRAY Application.Transpose（Application.Transpose（Range1.Value）

您可能需要做其他操作，如果它是多维的范围。我没有测试过。

以下是一些建议：

 子DataStats1（）
昏暗范围1由于范围
昏暗MYARRAY为Variant
昏暗的V作为变
昏暗的Ð作为对象设置范围1 = Application.InputBox（选择范围1：标题：=设置数据范围中输入：= 8）MYARRAY = Application.Transpose（Application.Transpose（Range1.Value））集合D =的CreateObject（的Scripting.Dictionary）对于每个EL在MYARRAY
   D（EL）= 1
下一个##分配密钥给数组：
V =运行起来也'##在这一点上，v是唯一值的数组。
做你想做的事情：

打印列表，一列新的工作表：
Sheets.Add
范围（A1）。调整（UBound函数（ⅴ）+ 1）。价值= Application.Transpose（v）的
'或打印列表中使用了MsgBox：
MSGBOX加入（五，，）'或打印到控制台：
Debug.Print加入（五，，）结束小组
 

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

解决方案

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

这篇关于删除重复在VBA数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！