Assign Arr() to Worksheet Range

[jim may]

Note Code below - Code line Marked (in Red) produces my Arr(1) 6 times,
1, 1, 1, 1, 1, 1

Versus my values Arr(1) thru Arr(6) of

1, 2, 3, 4, 5, 6

Trying to solve array mystery, right now..

Thanks,

Jim

Option Base 1

Sub GetNonZeroCells()
Dim Arr()
Dim C As Range
Dim Rng As Range
Dim i As Integer
i = 1
Set Rng = Range("H10:H20")
With Rng
For Each C In Rng
    If C <> 0 Then
        ReDim Preserve Arr(i)
        Arr(i) = C.Value
        i = i + 1
    End If
Next C
End With
i = i - 1
ActiveSheet.Range("J10:J" & i + Range("J10").Row() - 1) = Arr   'SEE HERE !!
End Sub

 

[VoG]

Hi Jim.

You need to transpose the array

ActiveSheet.Range("J10:J" & i + Range("J10").Row() - 1) = Application.Transpose(Arr)

 

[jim may]

VoG, As always thanks -- Is there an brief underlying reason for the "Transpose"?
Jim

 

[VoG]

Counter-intuitive I know but think of a one-dimensional array as being horizontal. You need to transpose it to vertical in order to write it to a column.

 

[jim may]

Seems LOGICAL to me; Got it!!
Tks,
Jim