title: Collection To Array Then To Range
body: <p>I have 2d matrix in Excel filled with numbers and zeros. I needed numbers from each row of matrix to be listed without blanks (here without zeros) that's why I added them to collection. Than I used function I found at Stackoverlow  for converting collection to array which I needed to write the rows back to Excel. The solution works almost fine except I get full row filled with numbers of the same value in case when there is only 1 number in the matrix. The problem seems to occur during writing back values to cells. Secondly how can I get rid of these #N/D in my results? Screen and code attached, thank you.</p>
  
  <p>!<a href="https://imgur.com/a/aP16DE1" rel="nofollow noreferrer">https://imgur.com/a/aP16DE1</a></p>
  
  <pre><code>Option Explicit
  Public Function CollectionToArray(myCol As Collection) As Variant
  
  Dim result  As Variant
  Dim cnt     As Long
  
  ReDim result(myCol.Count - 1)
  
  For cnt = 0 To myCol.Count - 1
  result(cnt) = myCol(cnt + 1)
  Next cnt
  
  CollectionToArray = result
  
  End Function
  
  Public Sub TestMe()
  
  Dim cell, k As Variant
  Dim i  As Integer
  Dim myCol As New Collection
  Dim grKol, Destination As Range
  
  Set grKol = Range("D4:BA4")
  Set Destination = Range("D20:R20")
  
  
  For i = 1 To 50
  If Application.WorksheetFunction.Sum(grKol.Offset(i - 1, 0)) = 0 Then 
  Exit For
  For Each cell In grKol.Offset(i - 1, 0)
      If cell &gt; 0 Then
      myCol.Add cell
      End If
  Next cell
  k = CollectionToArray(myCol)
  Destination.Offset(i, 0) = k
  Set myCol = Nothing
  Next i
  
  
  End Sub
  </code></pre>