2d array pasting into single column

zpierucci

New Member
Joined
Sep 5, 2019
Messages
42
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top