'
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
