Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text from multiple columns to one
Sub MakeOneCol()
Dim ThisRow As Range Dim NewVal As String Dim i, j As Integer Set ThisRow = ActiveCell For i = 0 To 9 NewVal = "" For j = 0 To 49 NewVal = NewVal & Format(ThisRow.Offset(i, j), 0) Next j ThisRow.Offset(i, 0) = NewVal Next i End Sub You will need to decide what you want to do with numbers and if you want spaces in-between each entry from each cell. Also, if there is a blank cell and you are putting spaces in-between do you want two spaces? This code starts at the active cell and uses the 10 rows and 50 cols. You can do anything you like to decide what range to process but you should be able to modify this code to get the results you want. If you need more help just ask. If all cells are numbers in this example you will get a totally different result than if some are not numbers. Chrissy. "T.K Kullervo" wrote in message ... Hi, Is there a way to get all the text from a row to the First column of the row. I tried going throw all the cells in a loop and saving the values of the A and B columns and concatting them together in A column. This works but its very slow. Is there some other way to do this text to columns backwords? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text from multiple columns to one
Yes, thank you, it works great.. Could you show me how i can delete the text
i have copied to the first column from the other columns. "Chrissy" wrote in message ... Sub MakeOneCol() Dim ThisRow As Range Dim NewVal As String Dim i, j As Integer Set ThisRow = ActiveCell For i = 0 To 9 NewVal = "" For j = 0 To 49 NewVal = NewVal & Format(ThisRow.Offset(i, j), 0) Next j ThisRow.Offset(i, 0) = NewVal Next i End Sub You will need to decide what you want to do with numbers and if you want spaces in-between each entry from each cell. Also, if there is a blank cell and you are putting spaces in-between do you want two spaces? This code starts at the active cell and uses the 10 rows and 50 cols. You can do anything you like to decide what range to process but you should be able to modify this code to get the results you want. If you need more help just ask. If all cells are numbers in this example you will get a totally different result than if some are not numbers. Chrissy. "T.K Kullervo" wrote in message ... Hi, Is there a way to get all the text from a row to the First column of the row. I tried going throw all the cells in a loop and saving the values of the A and B columns and concatting them together in A column. This works but its very slow. Is there some other way to do this text to columns backwords? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text from multiple columns to one
And another problem surfaced. Because the lenght of the text may change,
when i have a shorter text for example one page, four blank pages remain. Is there someway to force the number of pages to the amount of text in the sheets. "Chrissy" wrote in message ... Sub MakeOneCol() Dim ThisRow As Range Dim NewVal As String Dim i, j As Integer Set ThisRow = ActiveCell For i = 0 To 9 NewVal = "" For j = 0 To 49 NewVal = NewVal & Format(ThisRow.Offset(i, j), 0) Next j ThisRow.Offset(i, 0) = NewVal Next i End Sub You will need to decide what you want to do with numbers and if you want spaces in-between each entry from each cell. Also, if there is a blank cell and you are putting spaces in-between do you want two spaces? This code starts at the active cell and uses the 10 rows and 50 cols. You can do anything you like to decide what range to process but you should be able to modify this code to get the results you want. If you need more help just ask. If all cells are numbers in this example you will get a totally different result than if some are not numbers. Chrissy. "T.K Kullervo" wrote in message ... Hi, Is there a way to get all the text from a row to the First column of the row. I tried going throw all the cells in a loop and saving the values of the A and B columns and concatting them together in A column. This works but its very slow. Is there some other way to do this text to columns backwords? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text from multiple columns to one
Use this instead.
Sub MakeOneCol() Dim ThisRow As Range Dim NewVal As String Dim i, j As Integer Dim iRows, iCols As Integer Set ThisRow = ActiveCell iCols = 50 ' change to the number you want iRows = 10 ' change to the number you want For i = 0 To iRows - 1 NewVal = "" For j = 1 To iCols NewVal = NewVal & Format(ThisRow.Offset(i, j), 0) Next j ThisRow.Offset(i, 0) = NewVal Next i Range(ThisRow.Offset(0, 1), ThisRow.Offset(iRows - 1, iCols)).Clear End Sub Let me know if you want any other changes made. Chrissy. "T.K Kullervo" wrote in message ... Yes, thank you, it works great.. Could you show me how i can delete the text i have copied to the first column from the other columns. "Chrissy" wrote in message ... Sub MakeOneCol() Dim ThisRow As Range Dim NewVal As String Dim i, j As Integer Set ThisRow = ActiveCell For i = 0 To 9 NewVal = "" For j = 0 To 49 NewVal = NewVal & Format(ThisRow.Offset(i, j), 0) Next j ThisRow.Offset(i, 0) = NewVal Next i End Sub You will need to decide what you want to do with numbers and if you want spaces in-between each entry from each cell. Also, if there is a blank cell and you are putting spaces in-between do you want two spaces? This code starts at the active cell and uses the 10 rows and 50 cols. You can do anything you like to decide what range to process but you should be able to modify this code to get the results you want. If you need more help just ask. If all cells are numbers in this example you will get a totally different result than if some are not numbers. Chrissy. "T.K Kullervo" wrote in message ... Hi, Is there a way to get all the text from a row to the First column of the row. I tried going throw all the cells in a loop and saving the values of the A and B columns and concatting them together in A column. This works but its very slow. Is there some other way to do this text to columns backwords? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text from multiple columns to one
Thanks a lot for your help.. It was very usefull. I Solved the problem by
defining the print area. Thanks again.. "Chrissy" wrote in message ... Yes - select the text and make that they print area. OR - delete the print area and only the area with text will be printed. ummmmmm - maybe I have not understood this. How about you be a bit more specific - the blank pages - are they from blank lines BELOW the data or from blank columns BESIDE the data? Chrissy. T.K Kullervo wrote And another problem surfaced. Because the lenght of the text may change, when i have a shorter text for example one page, four blank pages remain. Is there someway to force the number of pages to the amount of text in the sheets. "Chrissy" wrote in message ... Sub MakeOneCol() Dim ThisRow As Range Dim NewVal As String Dim i, j As Integer Set ThisRow = ActiveCell For i = 0 To 9 NewVal = "" For j = 0 To 49 NewVal = NewVal & Format(ThisRow.Offset(i, j), 0) Next j ThisRow.Offset(i, 0) = NewVal Next i End Sub You will need to decide what you want to do with numbers and if you want spaces in-between each entry from each cell. Also, if there is a blank cell and you are putting spaces in-between do you want two spaces? This code starts at the active cell and uses the 10 rows and 50 cols. You can do anything you like to decide what range to process but you should be able to modify this code to get the results you want. If you need more help just ask. If all cells are numbers in this example you will get a totally different result than if some are not numbers. Chrissy. "T.K Kullervo" wrote in message ... Hi, Is there a way to get all the text from a row to the First column of the row. I tried going throw all the cells in a loop and saving the values of the A and B columns and concatting them together in A column. This works but its very slow. Is there some other way to do this text to columns backwords? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for text in multiple columns | Excel Discussion (Misc queries) | |||
Count Text Across Multiple Columns | Excel Worksheet Functions | |||
Text to Columns for Multiple Worksheets | Excel Worksheet Functions | |||
using CSV to break up text into multiple columns | Excel Discussion (Misc queries) | |||
splitting text to multiple columns | Excel Discussion (Misc queries) |