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?

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?

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?

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?

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?