Concatnating issues

P

punter

I have weird one here. I have two columns in excel with written data i
them. I want to take the second column and concatenate it into th
first column. However, I do not want the data from the second colum
to start immediatley after the data from the first. Example:



Column A Colum
B


It is a nice day today. I hope its nic
tomorrow.



If I concatenate these together this is what I get:



Column A

It is a nice day today. I hope its nice tomorrow.


I want it to leave a few rows between the data. Example:


Column A

It is a nice day today.


I hope its nice tomorrow.




There are about three hundred cells of data that I have to do this to.
If it wasn't that much I would just enter the extra space manually.
Any help you could give would be great.

Thanks

Eddi
 
B

Bernie Deitrick

Eddie,

When you say you want to leave a few rows between the data, do you mean you
want a few Excel rows (data in A1, then the other data in A3 or A4 or A5) or
do you mean you want the data in the same cell, but have it wrapped with
some extra line feeds? Either way is possible, but obviously their solutions
differ.

Also, how is your data arranged? How many rows? Any blanks?

HTH,
Bernie
MS Excel MVP
 
P

punter

I want the data in the same cell, but have it wrapped with
some extra line feeds. For example, I would like about two "entered
spaces between the original data in the cell and data concatenated in



Thank
 
B

Bernie Deitrick

punter,

The macro below will combine the data from columns A and B into column A.
Assumes: your data starts in A1, and is contiguous in column A (no blanks).

You didn't say what you wanted to do with column B, but once you run the
macro, you can delete it if you want.

HTH,
Bernie
MS Excel MVP

Sub MacroForPunter()
Dim myCell As Range
Dim myRange As Range

Set myRange = Range(Range("A1"), Range("A1").End(xlDown))
myRange.WrapText = True

For Each myCell In myRange
myCell.Value = myCell.Value & Chr(10) & Chr(10) & myCell(1, 2).Value
Next
End Sub
 
Top