Write Array to Excel Range

Write Array to Excel Range

This is an excerpt from method of mine, which converts a DataTable (the dt variable) into an array and then writes the array into a Range on a worksheet (wsh var). You can also change the topRow variable to whatever row you want the array of strings to be placed at.

object[,] arr = new object[dt.Rows.Count, dt.Columns.Count];
for (int r = 0; r < dt.Rows.Count; r++)
{
    DataRow dr = dt.Rows[r];
    for (int c = 0; c < dt.Columns.Count; c++)
    {
        arr[r, c] = dr[c];
    }
}
Excel.Range c1 = (Excel.Range)wsh.Cells[topRow, 1];
Excel.Range c2 = (Excel.Range)wsh.Cells[topRow + dt.Rows.Count - 1, dt.Columns.Count];
Excel.Range range = wsh.get_Range(c1, c2);
range.Value = arr;

Of course you do not need to use an intermediate DataTable like I did, the code excerpt is just to demonstrate how an array can be written to worksheet in single call.

Writing an array to a range. Only getting first value of array

Do this:

arrayData = Array("A", "B", "C", "D", "E")

[a1].Resize(UBound(arrayData) + 1) = Application.Transpose(arrayData)

The important bit is the Transpose() function.

But it is better to work with 2D arrays from the get go if you plan on writing them to the worksheet. As long as you define them as rows in the first rank and columns in the second, then no transposition is required.

c# interop excel: Write 1D array to excel range

I seriously don't know what exactly is wrong with the above methods. But I found the solution:

Excel.Range range = sheetSource.UsedRange.Columns[columnIndx];
range.Value = application.WorksheetFunction.Transpose(Ary);

Fast way to output array into range

Simple examples for a 1-D array

array to row:

Sub arrayTest()
    Dim arr(1 To 3) As String
    Dim r1 As Range
    Set r1 = Range("A1:C1")

    arr(1) = "larry"
    arr(2) = "moe"
    arr(3) = "curly"

    r1 = arr
End Sub

or array to column:

Sub arrayTest2()
    Dim arr(1 To 3) As String
    Dim r1 As Range
    Set r1 = Range("A1:A3")

    arr(1) = "larry"
    arr(2) = "moe"
    arr(3) = "curly"

    r1 = Application.WorksheetFunction.Transpose(arr)
End Sub

A 2-D array is equally easy:

Sub arrayTest3()
    Dim arr(1 To 3, 1 To 2) As String
    Dim r1 As Range
    Set r1 = Range("A1:B3")

    arr(1, 1) = "larry"
    arr(2, 1) = "moe"
    arr(3, 1) = "curly"
    arr(1, 2) = "gary"
    arr(2, 2) = "student"
    arr(3, 2) = "whatever"

    r1 = arr

End Sub

EDIT#1:

Say we have an arbitrary 1-D array, either zero-based or one-based, and we want to push it into a row starting with cell B9:

Sub arrayTest4()
    Dim r1 As Range, U As Long, L As Long, rBase As Range
    Set rBase = Range("B9")
    arr = Array("qwert", 1, 2, 3, 4, "ytrew", "mjiop", "nhy789")

    L = LBound(arr)
    U = UBound(arr)
    Set r1 = rBase.Resize(1, U - L + 1)

    r1 = arr

End Sub

VBA: Write array to sheet in a block (one memory access)

On this site I found this useful little piece...

Dim Destination As Range
Set Destination = Range("K1")
Destination.Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr

You can transpose the array when writing to the worksheet:

Set Destination = Range("K1")
Destination.Resize(UBound(Arr, 2), UBound(Arr, 1)).Value = Application.Transpose(Arr)

---

Related Topics