Transfering dictionary to array

[tiredofit]

The code below works to remove duplicates:

Option Explicit

Sub test()

Dim DIC As Scripting.Dictionary

Set DIC = New Scripting.Dictionary

Dim MyArray() As Variant

Columns(3).ClearContents

MyArray = Cells(1, 1).CurrentRegion.Value

Dim n As Long

For n = 1 To UBound(MyArray, 1)
    
    DIC.Item(MyArray(n, 1)) = 0
    
Next n

MyArray = DIC.Keys

Dim NewArray() As Variant
ReDim NewArray(1 To DIC.Count, 1 To 1) As Variant

Dim a As Long

For a = 1 To DIC.Count

    NewArray(a, 1) = MyArray(a - 1)

Next a

Cells(1, 3).Resize(DIC.Count, 1).Value = NewArray

Set DIC = Nothing

End Sub

The question I have is why would I need to create NewArray?

I tried this but it returned nothing:

Option Explicit

Sub test2()

Dim DIC As Scripting.Dictionary

Set DIC = New Scripting.Dictionary

Dim MyArray() As Variant

Columns(3).ClearContents

MyArray = Cells(1, 1).CurrentRegion.Value

Dim n As Long

For n = 1 To UBound(MyArray, 1)
    
    DIC.Item(MyArray(n, 1)) = 0
    
Next n

MyArray = DIC.Keys

'Neither this:


Cells(1, 3).Resize(DIC.Count, 1).Value = MyArray

' nor this works

Cells(1, 3).Resize(DIC.Count).Value = MyArray​
Set DIC = Nothing

End Sub

Thanks

 

[Peter_SSs]

Try

Cells(1, 3).Resize(DIC.Count, 1).Value = Application.Transpose(MyArray)

or directly from the dictionary without any new array

Cells(1, 3).Resize(DIC.Count) = Application.Transpose(DIC.keys)

 

[tiredofit]

Try

Cells(1, 3).Resize(DIC.Count, 1).Value = Application.Transpose(MyArray)

or directly from the dictionary without any new array

Cells(1, 3).Resize(DIC.Count) = Application.Transpose(DIC.keys)

​

​

Thanks

Can you explain why my original attempt returned no results?

 

[Peter_SSs]

Can you explain why my original attempt returned no results?

If it is producing nothing then I'm assuming that cell A1 is itself blank, even though Cells(1,1).current region might produce an array with several values.
Your code is actually not producing nothing, but is producing a column of values all equal to the first value in the new myArray (blank?)
Put a value in cell A1 and try again with your code and you should see what I mean. For example, for this sample data in column A, your code is producing what you see in column C

Book1
	A	B	C
1	a		a
2	b		a
3	c		a
4	b
5	c
6	b
7	c
8	b
9	c
10	b
11
Dic array

The issue is that the array you are producing from DIC.Keys is a horizontal array and you are trying to write it into a vertical space in the worksheet , so on each row you just get the first element in the array.

 

[tiredofit]

If it is producing nothing then I'm assuming that cell A1 is itself blank, even though Cells(1,1).current region might produce an array with several values.
Your code is actually not producing nothing, but is producing a column of values all equal to the first value in the new myArray (blank?)
Put a value in cell A1 and try again with your code and you should see what I mean. For example, for this sample data in column A, your code is producing what you see in column C

	A	B	C
a	a
b	a
c	a
b
c
b
c
b
c
b

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Dic array​

The issue is that the array you are producing from DIC.Keys is a horizontal array and you are trying to write it into a vertical space in the worksheet , so on each row you just get the first element in the array.

Thanks for your detailed explanation.

BTW, cell A1 is NOT blank, otherwise my first code (the one at the start of this thread) would not have worked.

 

[Peter_SSs]

BTW, cell A1 is NOT blank, otherwise my first code (the one at the start of this thread) would not have worked.

It would still work provided A2 is not also blank.

If A1 is not blank, none of your codes produce "nothing" for me - ref my screen shot in post 4.

 

[tiredofit]

It would still work provided A2 is not also blank.

If A1 is not blank, none of your codes produce "nothing" for me - ref my screen shot in post 4.

Thanks, I reviewed my code and if I did this:

Cells(1, 3).Resize(DIC.Count).Value = MyArray

it returned just the first element multiple times.

As you correctly pointed out, this is to do with 1 dimensional arrays and the need to transpose them.

Thanks again for your help.

 

[Peter_SSs]

Thanks again for your help.

You're welcome.