Transfer Array data to Spreadsheet

[removed - misinterpeted, Kris has posted a solution specific to the question]

Sub kTest()
Dim MyArray, MyNewArray()

MyArray = [a1:c3]

ReDim MyNewArray(1 To UBound(MyArray, 1) * UBound(MyArray, 2), 1 To 1)

'before

'/ A 1 2
'/ B 2 3
'/ C 3 4

'after

'/ A1
'/ A2
'/ B2
'/ B3
'/ C3
'/ C4

For i = 1 To UBound(MyArray, 1)
    For j = 2 To UBound(MyArray, 2)
        n = n + 1
        MyNewArray(n, 1) = MyArray(i, 1) & MyArray(i, j)
    Next
Next
[d1].Resize(n).Value = MyNewArray
End Sub

I don't really understand what you want to do and if in fact if Kris' approach (which does what you initially requested) is what you want or not ?

The INDEX I suggested was based on the fact that I had no clue as to what you were trying to achieve, I merely encased within the COUNTA to show that you could work with all values from one vector in that manner, eg:

Dim MyArray: MyArray = Sheets("Sheet1").Range("A1:C3")
Sheets("Sheet2").Range("A1").Resize(UBound(MyArray,1)).Value = Application.Index(MyArray,0,2)

would return the second column of array to A1:Ax on Sheet2 etc... but I'm not sure this is what you want to do ...

Do you mean something like this?

This code shows how you can extract a complete column or row of data from an Array onto a worksheet.

Sub test()

Dim MyArray

    'quick method to create an array to test with
    MyArray = [A1:C4]
    
    'Extract row 1 of the array into a row of cells starting at E1
    [E1].Resize(1, UBound(MyArray, 2) - LBound(MyArray, 2) + 1) = WorksheetFunction.Index(MyArray, 1, 0)

    'Extract column 2 of the array into a column of cells starting at E3
    [E3].Resize(UBound(MyArray, 1) - LBound(MyArray, 1) + 1, 1) = WorksheetFunction.Index(MyArray, 0, 2)
    
End Sub

Test Data in cells A1:C4 (avoided using letters and numbers as test data as that just confuses the issue! )

Apple	Fruit	Green
Beef	Meat	Brown
Cheese	Dairy	Yellow
Bread	Bakery	White

Guys, just for ref. (given prevalence in this thread) it is my understanding that

[A1]

is slower than

Range("A1") / Cells(1,1) / Cells(1,"A") etc...

Yes, see syntax in post prior to Phil_V

Originally Posted by wjones

Yes, that is the concept. I just need to digest it and use it in my code!

Is it possible to use 'Cells' instead of [range].resize?

e.g. [a1].resize to cells(1,1).resize

Thank you for your help!

YES

In fact I would normally use "cells(1,1)", but decided to use the [a1] format as that was what you seemed to have used

The code altered would be:

Sub test()

Dim MyArray

    'quick method to create an array to test with
    MyArray = Range(Cells(1,1), Cells(4,3))
    
    'Extract row 1 of the array into a row of cells starting at E1
    Cells(1,5).Resize(1, UBound(MyArray, 2) - LBound(MyArray, 2) + 1) = WorksheetFunction.Index(MyArray, 1, 0)

    'Extract column 2 of the array into a column of cells starting at E3
    Cells(3,5).Resize(UBound(MyArray, 1) - LBound(MyArray, 1) + 1, 1) = WorksheetFunction.Index(MyArray, 0, 2)
    
End Sub