Page 1 of 3 123 LastLast
Results 1 to 10 of 29
Like Tree18Likes

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

This is a discussion on VBA - copy 2-dimensional array into 1-dimensional (single column) within the Excel Questions forums, part of the Question Forums category; Hello, I have a (probably) simple problem but could not solve it for the life of me. I need to ...

  1. #1
    New Member
    Join Date
    Jan 2015
    Location
    Croatia
    Posts
    42

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

    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!

  2. #2
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,149

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

    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
    DocAElstein and astrbac like this.

  3. #3
    Board Regular ask2tsp's Avatar
    Join Date
    Feb 2015
    Location
    Almere Netherlands
    Posts
    394

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

    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 and astrbac like this.

  4. #4
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336

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

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

    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

  5. #5
    New Member
    Join Date
    Jan 2015
    Location
    Croatia
    Posts
    42

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

    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!

  6. #6
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336

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

    Quote Originally Posted by astrbac View Post
    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 by DocAElstein; Dec 15th, 2015 at 06:03 AM. Reason: A .Valuue..... wonk

  7. #7
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    7,436

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

    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
    DocAElstein and JaneA58 like this.


  8. #8
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    18,756

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

    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
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  9. #9
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336

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

    Quote Originally Posted by JackDanIce View Post
    .........., 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 )



    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 , , )

    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 by DocAElstein; Dec 15th, 2015 at 10:03 AM. Reason: Ding Dong.... or..... Hii Jack a question added

  10. #10
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336

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

    Quote Originally Posted by pgc01 View Post
    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-q...ml#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..
    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 by DocAElstein; Dec 15th, 2015 at 11:54 AM.

Page 1 of 3 123 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com