Need VBA to join cells into one single cell in other column

[skyport]

Need VBA to join cells in column A and put into one single cell in column B. However, the result in column B needs to retain bold and underlining aspects that exist in some of the data in part of each cell in column A

I am currently using the following code which joins the cells ok, however does not retain the parts in bold or underlined that are in column A. Hoping someone can help.

Sub JoinThem2()
Dim i As Long
Dim s As String

For i = 1 To Range("E" & Rows.Count).End(xlUp).Row
s = s & Cells(i, "E").Value
Next i
Range("K1").Value = s
End Sub

 

[Teeroy]

Roughly how many cells are you joining and what is the total number of characters in K1 when you are finished?

 

[skyport]

the amount of cells could be as many as a few dozen although I only tested your code with 10. Characters landing in cell K1 in unknown until the the program shows a result although is would certainly be under the max cell limit

 

[Teeroy]

What version of excel are you running?

And can you post some sample data that has failed? (I can't replicate the failure)

 

[skyport]

a cell would contain data such as: primary reason for joining would be health and happiness.Step 1: sign up. Step 2: would be to actively participate

there could be a lot more data in cell however, the above is a sample

both 2000 and 2007 Excel

 

[Teeroy]

I tried your example data using my code in excel 2010 and it worked for me.

I would expect it the code to work on excel 2007, but I don't think the characters property existed in excel 2000.

 

[skyport]

I would be more than happy if I could get it to work on the 2007

 

[Teeroy]

Unfortunately I only have excel 2010 and 2013 to test with.
What happens when you step through the macro using a small data set (it can take a while)? Execute it by using the F8 key from the VBE window and F8 to advance line by line and watch the results.

 

[skyport]

Sorry but I don't follow you on that one. Can you clarify?

 

[Peter_SSs]

I have tested Teeroy's code in Excel 2007 with about 20 cells, half of which had text as shown in post #14 and about half with shorter text but with some underlined/bold. The total number of characters was >1,000.

It took a little while to run (not surprisingly) but produced the required results as far as I can see.

 

[skyport]

Amazing. I cannot figure why it won't work on either computer here.