Moving mutliple columns into one column

[backfromthebush]

hi all,

I have a spreadsheet that has a matrix, which currently has 36 columns and 36 rows, but sometimes may contain more or less. Each cell within this range contains data. I need a macro that will copy all the data within this range and paste it into one column only. I dont mind what order the data is in. I.e. current range of data is B2:AI35 (total 1156 cells). I would like all this data in A1:A1156.

Can anyone help?

 

[Peter_SSs]

... currently has 36 columns and 36 rows, ... current range of data is B2:AI35

BTW, B2:AI35 is 34 x 34 not 36 x 36.



I'm assuming that there is headings in row 1. If that is not the case the code will need a bit of tweaking. Test in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> MatrixToColumn()<br>    <SPAN style="color:#00007F">Dim</SPAN> aMatrix, aColumn<br>    <SPAN style="color:#00007F">Dim</SPAN> R <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, C <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Columns("A").ClearContents<br>    <SPAN style="color:#00007F">With</SPAN> Range("B1").CurrentRegion<br>        aMatrix = .Offset(1).Resize(.Rows.Count - 1).Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    R = <SPAN style="color:#00007F">UBound</SPAN>(aMatrix, 1)<br>    C = <SPAN style="color:#00007F">UBound</SPAN>(aMatrix, 2)<br>    <SPAN style="color:#00007F">ReDim</SPAN> aColumn(1 <SPAN style="color:#00007F">To</SPAN> R * C, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> R<br>        <SPAN style="color:#00007F">For</SPAN> j = 1 <SPAN style="color:#00007F">To</SPAN> C<br>            k = k + 1<br>            aColumn(k, 1) = aMatrix(i, j)<br>        <SPAN style="color:#00007F">Next</SPAN> j<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Range("A1").Resize(R * C).Value = aColumn<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>