VBA - copy 2-dimensional array into 1-dimensional (single column)

astrbac

Board Regular
Hello,

I have a (probably) simple problem but could not solve it for the life of me. I need to copy 2-dimensional array from one sheet into a single column on another sheet.

Example:

Sheet1, data is in a range C2:N225

needs to be copied (transposed) onto

Sheet2, in a range D2:D2680

> First row of the array (Sheet1!C2:N2) goes into Sheet2!D2:D14
> Second row (Sheet1!C3:N3) goes into Sheet2!D15:D27
> ... and so forth

Many thanks!
 

shg

MrExcel MVP
One way:

Code:
Sub AB()
  Dim rRow          As Range
  Dim nCol          As Long
  Dim iOfs          As Long

  Application.ScreenUpdating = False
  With Sheet1.Range("C2:N225")
    nCol = .Columns.Count
    For Each rRow In .Rows
      rRow.Copy
      Sheet2.Range("D2").Offset(iOfs).PasteSpecial Transpose:=True
      iOfs = iOfs + nCol
    Next rRow
  End With
  Application.ScreenUpdating = True
End Sub
 

ask2tsp

Active Member
You could also do it like this
Code:
Sub matrixToVector()
   Dim i   As Integer
   Dim rng As Range
   
   Set rng = sheet1.Range("C2").CurrentRegion
   'note that this works for any dimension
   For i = 1 To rng.Count
      sheet2.Cells(1 + i, 4) = rng(i)
   Next i
End Sub
 

DocAElstein

Banned user
And a Ding Dong Rowg of a VBA Array version..., simplified first then opened up in an attempt to explain it....

Rich (BB code):
'
Sub aStrackbacArrayDingDongRowgSHimpfGlified()

    For Jay = 1 To UBound(ThisWorkbook.Worksheets("Sheet1").Range("C2:N225").Value) Step 1
    ThisWorkbook.Worksheets("Sheet2").Range("D2").Offset(((Jay - 1) * UBound(Application.Index(Cells, Jay + 1, Evaluate("column(C:N)")))), 0).Resize(UBound(Application.Index(Cells, Jay + 1, Evaluate("column(C:N)"))), 1).Value = Application.WorksheetFunction.Transpose(Application.Index(Cells, Jay + 1, Evaluate("column(C:N)")))
    Next Jay

End Sub
'
'
'
'
'
'

Sub aStrackbacArrayDingDongRowg() 'http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column.html
'1) Some Worksheets info.
Dim ws1 As Worksheet, ws2 As Worksheet 'Give Variable Mehtod, Properites etc of Worksheets Object, ( thereafter in code obtainable by tayping . Dot
Set ws1 = ThisWorkbook.Worksheets("Sheet1"): Set ws2 = ThisWorkbook.Worksheets("Sheet2")
Dim Dong() As Variant 'Main data Array. Although we know the type and size, for speed / convenience, the VBA .Range Method is used to assign the values of the Array to the Range which returns a field of Variant Elements.
Let Dong() = ws1.Range("C2:N225").Value 'Allowed VBA "one liner" to assign Values in a Spreadsheet Range to the Elements of an Array.

'2) A Ding Dong Rowg Looping to Fill an Output Array.
Dim clms() As Variant 'A required Array of column Indicies for a "majic" code line.  They are the column indicies we want to select from an Array. They can be string or long type, but for covenience we often use the VBA Array Function that returns a Field of Variant type elements
Dim clmCnt As Long: Let clmCnt = UBound(Dong(), 2) ' "Column" size of Input Data Array.    ( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
Dim rws() As Variant 'We require a 2 D , 1 column ( "vertical" ) type Array of the "row" indicies we use in the  "Magic Code line"
ReDim rws(1 To 1, 1 To 1) 'In our case we have a 1 Elemnent Array, but typically this would have more than 1 row
Dim Cntrws As Long: Let Cntrws = UBound(Dong(), 1) ' "Row" size of Input Data Array
Dim Ding() As Variant ' An Array for each of our "sliced rows" A dynamic Array Type is chosen as despite knowing the size, and possibly knowing the type, will be creating it with a "magic" code line which uses .Index Method which returns an Field of Variant Elements  ( The "Magic Code line is discussed here    http://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html  )
Dim Rowg() As Variant ' An Intermediate unecerrsary step for demonstration purposes. A "Stack" Array. This will be an Array of our sliced row Arrays. I probably could guess let the Elements are Strings, but i need the same type to allow me to equate ####  to Ding
ReDim Rowg(1 To Cntrws) 'We Knew the size, but use ReDim just because Dim only takes numbers
Dim Jay As Long 'Loop Bound variable

'3) Going "down" rows in our data in this loop to obtain the require Indicies for our "Magic Code line" and applyingm that "Magic Code line" To get a "row slice"
    For Jay = 1 To Cntrws Step 1
    Let rws(1, 1) = Jay + 1 'We use the entire "cells" as the Grid in our "magic Code line" below , Rather than typically with a Dong() Array Array below
    Let clms() = Evaluate("column(C:N)") 'For convenience use the Spreadsheet Function Column to return a 1 D "pseudo" Horizontal Array of the required column Indicies.
    Let Ding() = Application.Index(Cells, rws(), clms()) ''This "Magic Code line" allows us to select the rows and columns we want from ( usually ) Array or Range ) No one knows quite how this works.     Using Cells rather than a more smaller Array or Grid is a further advancement to the mystery..  http://www.mrexcel.com/forum/excel-questions/899838-merge-multiple-ranges-into-one-array-visual-basic-applications.html?#post4339672
    Let rws(1, 1) = Jay ' "Rows" in the Dong() Array start at 1
    Let clms() = Evaluate("column(A:L)") 'We need 1:12 here as Dong() has columns 1, 2, 3, .....12
    Let Ding() = Application.Index(Dong(), rws(), clms()) 'More typical use using an Array as first Argument
    Let Rowg(Jay) = Ding() 'Just to demo that dynamic we can equate Arrays of same size and type
    Next Jay

'4) Loop to Paste out each element from the Stack Array at a time
    For Jay = 1 To Cntrws Step 1
    ws2.Range("D2").Offset(((Jay - 1) * clmCnt), 0).Resize(clmCnt, 1).Value = Application.WorksheetFunction.Transpose(Rowg(Jay)) 'The Top left Range object ( Cell )of the start of our Output Range has the Offset Property of Jay x the "column" count to return a new  Range Object ( cell ) at the start Point of our next Output. This has Further the Resize Property applied to returna a further Range object , sized to suit the "column" count and this is then given the transposed "sliced row" for Row Jay. Then the Allowed VBA "one liner" is used to assign values from Elements of an Array to the cells in a Spreadsheet Range.
    Next Jay

End Sub
 

astrbac

Board Regular
Guys, thank you SO much! I ended up using the first solution and am now studying the others line by line. May have additional questions :)

Thanks again!
 

DocAElstein

Banned user
Guys, thank you SO much! I ended up using the first solution and am now studying the others line by line.
You are welcome, Glad we could help.
_ My code may be a bit over the top, but i find it always beneficial to see alternative solutions. ( And sorry, I was “learning as I went along” !! ) Possibly mine is the fastest method theoretically, as the "Array" approach minimises the interaction with the Spreadsheet ( Spreadsheet interaction usually slows things down a bit) . But if speed is not a major consideration the others are probably easier to understand and a better/ simpler alternative.

_ The basic difference and very brief explanation to help you understand:

_a) shg code copies each row as a range Object and pastes it out transposed . So Copies and pastes 244 times. This code maintains cell formatting, or rather copies it across ( Down ) in the sheet2 from sheet1

_b) ask2tsp code was a new one on me, using the characteristic that with referring to a range with just one argument, like Cells(i) or Range(i) , i is the “number” of the cell which counts left to right and then top to bottom..... or “....i is an index from right to left then top to bottom.....)
Referring to Ranges in VBA | Excel Matters
_.... ( careful it is snowing just now at that site above )
So ask2tsp code works through cell by cell in your input range and puts each value sequentially down your range in Sheet 2. Simple but effective and very easy to understand. I would maybe take the liberty if ask2tsp does not mind of doing this

Worksheets("Sheet2").Cells(1 + i, 5).Value = rng(i).Value

As i think that shows that you put each value in as I explained. VBA I think guesses and puts .Value in in this case, but relying on those defaults is for a beginner ( like me ) a bit dangerous. So this code puts only Values in. Format is not taken across.


_c) My second code takes all data into an Array in one go. Then it pastes out an Array of a transposed Row, 244 times. It also repeats a few steps, unnecessarily, doing things in different ways just because i was learning as I went along.
The second code ( i think ) , skips out bringing in the data and sort of uses the entire Spreadsheet as the “Pseudo” Input “Array”, and picks out an Array of row Values at a time from that and puts that Array of Values transposed into the appropriate place in sheet2. So My codes also only puts Values in. Format is not taken across.

Let us know if you need more help.

Alan

P.s.1. Small point: my code could be modified easilly to do similar to ask2tsp code to run on any range size staring at C2.

P.s.2. ask2tsp code as it is I think may not work if you have a complete empty row or column –This is because , the CurrentRange Property he uses returns a Range Object of a “box” of all cells “connected” through cells to the original Range Object ( C2 in your case ) to which it is applied. So the "box" would not extend past or "skip over" an emplty column or row. Again various simple mods can be done to overcome this
 
Last edited:

JackDanIce

Well-known Member
Although the range read into the array is hardcoded in the code, it's not difficult to make this dynamic so that aside, this suggestion is a hybrid of using an array and interacting with the spreadsheet directly:
Code:
Sub macro1()

    Dim arr()       As Variant
    Dim rng         As Range
    Dim x           As Long
    
    Application.ScreenUpdating = False
    
    arr = Sheets("Sheet1").Range("C2:N225").value
    
    For x = LBound(arr, 2) To UBound(arr, 2)
        With Sheets("Sheet2")
            Set rng = .Cells(.rows.count, 4).End(xlUp).Offset(1)
                rng.Resize(UBound(arr, 1)).value = Application.Index(arr, , x)
            Set rng = Nothing
        End With
    Next x
        
    Erase arr
        
    Application.ScreenUpdating = True
    
End Sub
 

pgc01

MrExcel MVP
Hi Alan

If you go for the Index/Evaluate you could go all the way and not loop at all, like:

Code:
Sub Test1()

Range("D2:D2689").Value = Application.Index(Range("Sheet1!$C$2:$N$225"), Evaluate("if(row($D$2:$D$2689),INT((ROW($D$2:$D$2689)-ROW($D$2))/COLUMNS(Sheet1!$C$2:$N$225))+1)"), Evaluate("if(row($D$2:$D$2689),mod(ROW($D$2:$D$2689)-ROW($D$2),COLUMNS(Sheet1!$C$2:$N$225))+1)"))

End Sub
 

DocAElstein

Banned user
.........., this suggestion is a hybrid of using an array and interacting with the spreadsheet directly:
..........
Hi .. Jack,
That was Nice,. I needed to do just that recently and did it similarly. So it was good to see an alternative. I often do a “middle” bit putting those “stacked bits” in a 1 D Array of Arrays. ( Just for fun ) Then paste that out in a loop. But “cutting out the middle bit” is of course better..”

BTW. You have not quite got, I think what he OP wanted.
You are “slicing out” each column and stacking those “sliced bits” out, one on top of the other.
The OP wants stacked out the transposed “sliced rows” one on top of the other
But i learnt a lot playing with your code and so then for fun added a second section _ 2) which stacks the transposed Rows one on top of the other. ( requires a transpose of the sliced row )



Rich (BB code):
Sub JackDoneNice() 'A Ding is a slice of a Dong, - get a Ding from a Dong and stack 'em out
Dim arrDong() As Variant, arrDing() As Variant 'A Ding is a slice of a Dong
Dim rng         As Range
Dim x As Long, y As Long  ' "Column" and "row" for use in "Slicing"    https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/
arrDong() = Sheets("Sheet1").Range("C2:N225").Value
Rem 1) 'Stack columns one on Top of the other
    For x = LBound(arrDong(), 2) To UBound(arrDong(), 2) 'For each "column" in Dong
        With Sheets("Sheet2")
            Set rng = .Cells(.Rows.Count, 4).End(xlUp).Offset(1)
                arrDing() = Application.Index(arrDong(), 0, x) 'Gives 2 D 1 "column" Array of all "row" elements in "column" x
                rng.Resize(UBound(arrDing, 1)).Value = arrDing 'Sliced Dong Column stacked out
            'Set rng = Nothing
        End With
    Next x
Sheets("Sheet2").Columns(4).ClearContents 'Clear column for next loop
Rem 2) 'Stack transposed rows one on Top of the other
    'Erase arrDong()
    For y = LBound(arrDong(), 1) To UBound(arrDong(), 1) 'For each "row" in Dong
        With Sheets("Sheet2")
            Set rng = .Cells(.Rows.Count, 4).End(xlUp).Offset(1)
                arrDing() = Application.Index(arrDong(), y, 0) 'Gives 1 D "pseudo" horizontal Array of all "column" elements in "row" y
                arrDing() = Application.WorksheetFunction.Transpose(arrDing()) 'Gives 2 D 1 "column" Array of all "row" elements in "row" y
                rng.Resize(UBound(arrDing, 1)).Value = arrDing() 'transposed Sliced Array row
            'Set rng = Nothing
        End With
    Next y

End Sub


_ ......................................................................

As we are “Hard Coding” for now I then went on for fun and modified your code slightly to take ( or rather “use” I think ) the whole spreadsheet “Cells” as the Index first Argument ( Grid , , ) so as to do away with the Spreadsheet interaction to make an Input data Array for the Index first Argument ( Grid , , )

Rich (BB code):
Sub JackCellsTransposeOrNotTransposeADingFromADong()
Dim arrDong() As Variant, arrDing() As Variant 'A Ding is a slice of a Dong
Dim rng As Range
Dim y As Long  ' "row" for use in "Magic Code line"
Rem 1) 'Get column indicies required to get Ding from a dong with "Magic Code line"   http://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html  )
Dim clms() As Variant: Let clms() = Evaluate("column(C:N)")
Rem 2) 'Stack transposed rows one on Top of the other
    For y = 1 + 1 To 224 + 1 'For each "row" in Dong
        With Sheets("Sheet2")
            Set rng = .Cells(.Rows.Count, 4).End(xlUp).Offset(1)
                arrDing() = Application.Index(Cells, y, clms()) 'Gives 1 D "pseudo" horizontal Array of all "column" elements in "row" y
                arrDing() = Application.WorksheetFunction.Transpose(arrDing()) 'Gives 2 D 1 "column" Array of all "row" elements in "row" y
                rng.Resize(UBound(arrDing, 1)).Value = arrDing() 'transposed Sliced Array row
            'Set rng = Nothing
        End With
    Next y
Sheets("Sheet2").Columns(4).ClearContents 'Clear column for next loop
Rem 3) Alternative "Magic Code line" Form.
Let clms() = Evaluate("row(3:14)") 'Using a 2 D 1 column Array for last ( "columns" ) argument in "Magic Code Line" has the effecof us getting a transposed Array compared with that in _2) above
    For y = 1 + 1 To 224 + 1 'For each "row" in Dong
        With Sheets("Sheet2")
            Set rng = .Cells(.Rows.Count, 4).End(xlUp).Offset(1)
                arrDing() = Application.Index(Cells, y, clms()) 'In this form the "Magic code line" gives the Array Transposed ( 2 D 1 "column" Array of all "row" elements in "row" y )
                rng.Resize(UBound(arrDing, 1)).Value = arrDing() 'transposed Sliced Array row
            'Set rng = Nothing
        End With
    Next y
End Sub
That code above then works similar to my second one and i have used some similar variables, but to save the eyes not 'commented it too much this time!!!
And while i was there i did a mod to my “Magic Code Line” bits that removes the need to transpose as the Index produced Array in that code section comes out transposed already then.
I guess we have this one as efficient as we can....maybe.... But i need to look at pgc’s stuff now... ( after a Coffee)

_ ...........................................................................

All great learning. Great Thread. Thanks for adding to it.
Alan



P.s. Can you tell me the reasoning to
Erase arr and Set rng = Nothing
_ . I guess it erases the Array and range from memory, declaration and all. Is that just a bit of good programming practice to do with memory conserving?
Thanks
 
Last edited:

DocAElstein

Banned user
Hi Alan
..
If you go for the Index/Evaluate you could go all the way and not loop at all, like:....
Hi pgc,
Wow. When I thought it got great, it just got better!
A real “one liner”

I really should have caught the idea of getting the entire Indices necessary to apply to the Index first Argument ( Grid, ___, ____ ) to get the Output Array in one line.
I guess i would have had to do that by looping in VBA so then having to do that by looping, and just coming up with another slightly modified version of my codes. So no Mega “one Liner” without looping like you did.

This could be really useful and interesting thing for me ( and others ) to learn.. I love these “one liner” things.. Unfortunately i have very little experience with Spreadsheet formulas ( I do get the point that you are using like me the VBA Evaluate(“____”) method to use Spreadsheet formulas, but just a lot more complicated formulas than i used. )

I have just done my head in for a couple of hours but i really cannot understand those formulas. I just lack the experience with Spreadsheet formulas. If you have the time, could you “open up” these formulas for me and explain them. I think that would be a great contribution to this thread.
If not, no worries, I will post back with an explanation... in about a month once I have understood it !!

Thanks, and thanks for adding to the thread with a great “one liner” – I did not think it was possible ( .. although maybe from that other famous thread
http://www.mrexcel.com/forum/excel-questions/806702-visual-basic-applications-evaluate-range-vlookup.html#post3944863
_ .. I guess I should have realised it was possible ! .. and that you could probably do it!
)

Alan


P.s. Along the way got a version to work using the Cells for the Index first Argument ( Grid, ___, ____ ) ... but that was all guess work and trial and error..
Rich (BB code):
'
'
Sub pgcDBMOneLiner()
Dim rws() As Variant
Dim clms() As Variant
Sheets("Sheet2").Columns(4).ClearContents 'Clear column

'pgc  Index first Argument ( Grid,   ,     ) is an Array . Do this first in column D
rws() = Evaluate("=if(row($D$2:$D$2689),INT((ROW($D$2:$D$2689)-ROW($D$2))/COLUMNS(Sheet1!$C$2:$N$225))+1)")
rws() = Evaluate("=if(row(2:2689),INT((ROW(2:2689)-ROW(2:2))/COLUMNS(Sheet1!$C$2:$N$225))+1)")
clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2689)-ROW(2:2),COLUMNS(Sheet1!$C$2:$N$225))+1)")
clms() = Evaluate("=if(row($D$2:$D$2689),mod(ROW($D$2:$D$2689)-ROW($D$2),COLUMNS(Sheet1!$C$2:$N$225))+1)")

Sheets("Sheet2").Range("D2:D2689").Value = Application.Index(Range("Sheet1!$C$2:$N$225"), rws(), clms())

'pgc  Index first Argument ( Grid,    ,    ) is "Spreadsheet" Do this in Column E
Sheets("Sheet2").Columns(5).ClearContents 'Clear column for next pgcDB "Mega One Liner"

rws() = Evaluate("=if(row(2:2689),INT((ROW(2:2689)-ROW(2:2))/COLUMNS(Sheet1!$C$2:$N$225))+2)")
clms() = Evaluate("=if(row(2:2689),mod(ROW(2:2689)-ROW(2:2),COLUMNS(Sheet1!$C$2:$N$225))+3)")
Sheets("Sheet2").Range("E2:E2689").Value = Application.Index(Cells, rws(), clms())

End Sub
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top