CODEWITHSUNDEEP
arraysvbaexcel
hornetbzz
hornetbzz

Reputation: 9357

VBA how to dump a 2D array into a worksheet Range

I'm looking for the nice and elegant solution to achieve this working code below. I could NOT find it after many searches and tries, since VBA rationales keep kind of magic for me.

The looping code :

Public Sub Array2Range(My2DArray As Variant, aWS As Worksheet)
' Ref : https://stackoverflow.com/questions/6063672/excel-vba-function-to-print-an-array-to-the-workbook
' Ref : http://www.ozgrid.com/VBA/sort-array.htm
' Ref : https://www.mrexcel.com/forum/excel-questions/14194-vba-arrays-examples-please-how-read-range-th.html
' Ref : https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm
' Usage : Array2Range MyArray, aWS

  Dim i, j As Integer
  Dim elt As Variant

  For i = 1 To UBound(My2DArray) - LBound(My2DArray) + 1
      j = 1
     For Each elt In My2DArray(i)
        aWS.Cells(j, i) = elt
        j = j + 1
      Next elt
  Next i

End Sub

The watches window screenshot, to understand the 2D array structure enter image description here

Thx for your support and ideas.

Upvotes: 0

Views: 3203

Answers (2)

Slai
Slai

Reputation: 22866

Similar, but a bit different alternative:

Sub ArrayToRange(jaggedArray, cell As Range): Dim subArray
    For Each subArray In jaggedArray
        cell.Resize(UBound(subArray), 1) = Application.Transpose(subArray)
        Set cell = cell(, 2)
    Next
End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152465

this eliminates one loop:

Public Sub Array2Range(My2DArray As Variant, aWS As Worksheet)
' Ref : https://stackoverflow.com/questions/6063672/excel-vba-function-to-print-an-array-to-the-workbook
' Ref : http://www.ozgrid.com/VBA/sort-array.htm
' Ref : https://www.mrexcel.com/forum/excel-questions/14194-vba-arrays-examples-please-how-read-range-th.html
' Ref : https://bettersolutions.com/excel/cells-ranges/vba-working-with-arrays.htm
' Usage : Array2Range MyArray, aWS

  Dim i as Long


  For i = 1 To UBound(My2DArray) - LBound(My2DArray) + 1
      aWS.Cells(1, i).Resize(UBound(My2DArray(i))).Value = Application.Transpose(My2DArray(i))
  Next i

End Sub

Upvotes: 1

Related Questions