Results 1 to 9 of 9

Thread: 2d array pasting into single column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 2d array pasting into single column

    I have 6 columns (B:G) with 16 rows (2:17) of data in each column. I am trying to get all of the info of the array into one single column starting at cell K4. I have been able to write a loop to go down the rows but I can't seem to get the loops right. Any suggestions?

  2. #2
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: 2d array pasting into single column

    When you say "write a loop" I assume you mean VBA. If so, here is a basic loop to do that:

    Code:
        r2 = 4
        For c = 2 To 7
            For r = 2 To 17
                Cells(r2, "K") = Cells(r, c)
                r2 = r2 + 1
            Next r
        Next c
    This is very basic so you can see the main idea. There are somewhat more complicated ways that are much faster. You probably wouldn't notice a difference on such a small sample though. It's also worth noting that you can do this with a formula as well.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2d array pasting into single column

    Thanks Eric. That worked perfect.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    257
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2d array pasting into single column

    What about
    Code:
    Sub test()
        c = 4
        ar = Range("b2:g17")
        For i = 1 To 6
            Range("k" & c & ":k" & UBound(ar, 1) + c - 1) = Application.Index(ar, 0, i)
            c = c + UBound(ar, 1)
        Next
    End Sub
    OR
    Code:
    Sub test()
        c = 4
        ar = Application.Transpose(Range("b2:g17"))
        For i = 1 To 16
            Range("k" & c & ":k" & UBound(ar, 1) + c - 1) = Application.Index(ar, 0, i)
            c = c + UBound(ar, 1)
        Next
    End Sub
    Last edited by mohadin; Sep 12th, 2019 at 05:21 PM.

  5. #5
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: 2d array pasting into single column

    mohadin showed a few other options, which would be more efficient on large samples. Here's one more:

    Code:
        For c = 2 To 7
            Range("B2:B17").Offset(, c - 2).Copy Range("K4").Offset((c - 2) * 16).Resize(16)
        Next c
    This used Copy instead of just moving the values. Depends if you want to keep the formatting too or not. The Offset and Resize methods can be really useful. Excel always has about 10 ways to do anything, it just depends on the situation.

    Glad we could help!
    Last edited by Eric W; Sep 12th, 2019 at 06:05 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  6. #6
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2d array pasting into single column

    Thanks guys. This is awesome. Not so worried about the formatting for this but good info. One more question, if I had to do the same thing but the columns or "ranges" were not contiguous? So I still had 6 rows of data but they were separated by other data?

  7. #7
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: 2d array pasting into single column

    Again, lots of ways to do this. Here's a slight modification of my original macro:

    Code:
    Sub test2()
    Dim cols As Variant, r As Long, c As Long, r2 As Long
    
    
        cols = Array(2, 3, 4, 5, 6, 7)
        r2 = 4
        For c = LBound(cols) To UBound(cols)
            For r = 2 To 17
                Cells(r2, "K") = Cells(r, cols(c))
                r2 = r2 + 1
            Next r
        Next c
    End Sub
    Just put the columns you want in the Cols array. If the columns are consistent distances apart, you can just add that amount to c every time.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  8. #8
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2d array pasting into single column

    Worked perfect again! Thanks a lot. Separate issue, I haven't done much VBA coding for years. I really enjoy it and am trying to get proficient in it again. Is there any online resources that you would recommend for practice problem sets or good exercises?

  9. #9
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,661
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    5 Thread(s)

    Default Re: 2d array pasting into single column

    One of the members here, @hiker95 , has compiled an enormous list of online resources for learning anything Excel-related, including VBA. Check out post 3 on this thread:

    https://www.mrexcel.com/forum/genera...excel-vba.html

    Find something on the list that looks interesting and start there. Glad we could help!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

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
  •