How to manipulate an 2D Array into a Column Array?
 

I have an array containing text words which are in a readable narrative.
The array is A1:CH13 (86 columns X 13 rows) a total of 1118 cells.

The text words read down the column A: (A maximum of 13)
then onto the next column B: which is the beggining of the next line and so
on.

However not all the cells in a given column of 13 are filled up with words.
That is, there are blanks at the end of some lines (columns)

Without using drag and drop 85 times how can I move all the columns
sequentially
into the A: column array so that it is still readable down the row and there
are no blank cells inbetween. The final array should end up A1:A??? where
A??? is < A1118

How to manipulate an 2D Array into a Column Array?
 

save a copy of your workbook as a backup, and with whatever sheet you want
modified as the active sheet try:

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


If you are new to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

"Joe" wrote:

I have an array containing text words which are in a readable narrative.
The array is A1:CH13 (86 columns X 13 rows) a total of 1118 cells.

The text words read down the column A: (A maximum of 13)
then onto the next column B: which is the beggining of the next line and so
on.

However not all the cells in a given column of 13 are filled up with words.
That is, there are blanks at the end of some lines (columns)

Without using drag and drop 85 times how can I move all the columns
sequentially
into the A: column array so that it is still readable down the row and there
are no blank cells inbetween. The final array should end up A1:A??? where
A??? is < A1118

How to manipulate an 2D Array into a Column Array?
 

Thanks JMB.
I tried it and it works.
Looks like I will have to learn Visual Basic macros.I was dreading it and
putting it off.

"JMB" wrote:

save a copy of your workbook as a backup, and with whatever sheet you want
modified as the active sheet try:

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


If you are new to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

"Joe" wrote:

I have an array containing text words which are in a readable narrative.
The array is A1:CH13 (86 columns X 13 rows) a total of 1118 cells.

The text words read down the column A: (A maximum of 13)
then onto the next column B: which is the beggining of the next line and so
on.

However not all the cells in a given column of 13 are filled up with words.
That is, there are blanks at the end of some lines (columns)

Without using drag and drop 85 times how can I move all the columns
sequentially
into the A: column array so that it is still readable down the row and there
are no blank cells inbetween. The final array should end up A1:A??? where
A??? is < A1118

How to manipulate an 2D Array into a Column Array?
 

You're welcome.

"Joe" wrote:

Thanks JMB.
I tried it and it works.
Looks like I will have to learn Visual Basic macros.I was dreading it and
putting it off.

"JMB" wrote:

save a copy of your workbook as a backup, and with whatever sheet you want
modified as the active sheet try:

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


If you are new to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.mvps.org/dmcritchie/excel/install.htm

"Joe" wrote:

I have an array containing text words which are in a readable narrative.
The array is A1:CH13 (86 columns X 13 rows) a total of 1118 cells.

The text words read down the column A: (A maximum of 13)
then onto the next column B: which is the beggining of the next line and so
on.

However not all the cells in a given column of 13 are filled up with words.
That is, there are blanks at the end of some lines (columns)

Without using drag and drop 85 times how can I move all the columns
sequentially
into the A: column array so that it is still readable down the row and there
are no blank cells inbetween. The final array should end up A1:A??? where
A??? is < A1118