I have 70 rows that is 11 columns deep that I need to put into one long
column using a macro. I want the rows to be consecutively loaded. A
smaller version of what I'm trying to do is this:

Turn this:
1 2 3
4 5 6
7 8 9

Into this:
1
2
3
4
5
6
7
8
9

This needs to be a macro because I receive this data once a day in the
incorrect format. Any advice you have would be appreciated!

Sub test()

Dim r As Long
Dim c As Long
Dim n As Long
Dim arr1
Dim arr2(1 To 770, 1 To 1)

arr1 = Range(Cells(1), Cells(70, 11))

For r = 1 To 70
For c = 1 To 11
n = n + 1
arr2(n, 1) = arr1(r, c)
Next
Next

Range(Cells(1), Cells(770, 1)) = arr2

End Sub


RBS


wrote in message
oups.com...
I have 70 rows that is 11 columns deep that I need to put into one long
column using a macro. I want the rows to be consecutively loaded. A
smaller version of what I'm trying to do is this:

Turn this:
1 2 3
4 5 6
7 8 9

Into this:
1
2
3
4
5
6
7
8
9

This needs to be a macro because I receive this data once a day in the
incorrect format. Any advice you have would be appreciated!

Select your data matrix and give a range name, e.g. myRange. Then give
the cell where your column will start a range name, e.g. "anchor".
Then use this code:

Sub BuildColumn()
Dim cell As Range, i As Integer

i = 1
For Each cell In Range("myRange")
Range("anchor").Cells(i) = cell
i = i + 1
Next

End Sub

wrote:
I have 70 rows that is 11 columns deep that I need to put into one long
column using a macro. I want the rows to be consecutively loaded. A
smaller version of what I'm trying to do is this:

Turn this:
1 2 3
4 5 6
7 8 9

Into this:
1
2
3
4
5
6
7
8
9

This needs to be a macro because I receive this data once a day in the
incorrect format. Any advice you have would be appreciated!

Try this:

Sub Test()
Dim i As Integer
Dim j As Integer
Dim k As Integer

i = 0
k = 1
Do While i < 70
j = 0
Do While j < 11
Range("D" & k) = Range("A1").Offset(i, j)
j = j + 1
k = k + 1
Loop
i = i + 1
Loop

End Sub

Assuming A1 is your starting point and D is your long column

--
renega
-----------------------------------------------------------------------
renegan's Profile: http://www.excelforum.com/member.php...fo&userid=1045
View this thread: http://www.excelforum.com/showthread.php?threadid=55247