Converting a matrix of data into a single column

[Hosley]

Hi there,

I want to take a matrix of data (e.g. 89 columns wide and 89 rows
wide), and order it all into one single column, where they data
currently in column B would follow all of the data currently in column
A, and so on. Currently I am simply cutting and pasting. Is there an
easier way to do this? I've tried making a macro for it but the one
made doesn't work. Any ideas?


Thanks,

Hos

 

[T. Valko]

See this:

http://tinyurl.com/2z6gmc

You can probably figure out how to adjust it to work for you.

Biff

 

[PY & Associates]

Dim rng As Range
For i = 2 To 89
Set rng = Range(Cells(1, i), Cells(1, i).End(xlDown))
rng.Cut Cells(Rows.Count, 1).End(xlUp)(2)
Next i

 

[PY & Associates]

Dim rng As Range
For i = 2 To 89
Set rng = Range(Cells(1, i), Cells(1, i).End(xlDown))
rng.Cut Cells(Rows.Count, 1).End(xlUp)(2)
Next i

 

[Hosley]

See this:

http://tinyurl.com/2z6gmc

You can probably figure out how to adjust it to work for you.

Biff

I think it worked! I used the code at http://tinyurl.com/2z6gmc.
Would you mind confirming the steps I took? I've never used Visual
Basic before, but I kind of guessed:

1. I copy and pasted the code:

Sub test()
Dim i As Long

For i = 2 To 86
Range(Cells(1, i), Cells(13, i)).Cut _
Cells(Rows.Count, 1).End(xlUp)(2, 1)
Next i

End Sub

2. Since my matrix is 89 X 89, I changed "For i = 2 To 86" to "For i =
2 To 89" and "Range(Cells(1, i), Cells(13, i)).Cut _" to
"Range(Cells(1, i), Cells(89, i)).Cut _"

3. It appears to work but it is difficult to double check. Also it
would be nice to have a general program that does not need specific
matrix size numbers. I'm guessing that this is what PY& Associates
(thanks for your help too!) provided, but I don't know to add this
code to Visual Basic.

Thanks!

Hos

 

[T. Valko]

I've never used Visual Basic before, but I kind of guessed

I do a lot of guessing myself!

Yes, you did it correctly.

And yes, PY is not hardcoding how many rows are involved.

Biff

 

[Hosley]

I do a lot of guessing myself!

Yes, you did it correctly.

And yes, PY is not hardcoding how many rows are involved.

Biff

Great, thanks!

Hos