Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array: Counting multiple values within array | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Use array to return array of values | Excel Worksheet Functions | |||
Goal Seek On Members of an Array within Array | Excel Worksheet Functions |