arrays - 删除 VBA 数组中的重复项

Question

代码工作正常。根据回复的帮助进行修改。

我有以下代码来从数组 MyArray 中删除重复项。代码在以下位置出现调试错误:d(MyArray(i)) = 1。错误是下标超出范围。不确定是什么原因造成的以及我的代码有什么问题。

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

Answer 1

您应该学会停止依赖Selection(这毕竟是您声明变量的原因......)。您可以改为使用 MyArray = Range1.Value。

现在，范围数组始终是二维的，如果您选择列范围，则实际上需要这样做:

MyArray = Application.Transpose(Range1.Value)

或者，如果您选择 ROW 范围:

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

如果是多维范围，可能需要进行其他操作。我没有测试过。

这里有一些想法:

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