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

 

[skyport]

Hi again Peter. hold on , I may have found the cause on my end

 

[skyport]

OK guys. thanks for your patience. I found what was interfering with it on my end and it now functions although it certainly does take a bit of time to process. Also, I wonder if there is a way to give me a choice of adding a space or a comma or period in between each cell of data that lands in K1.

 

[Teeroy]

I thought it may be slow since you have to access the range for every character.
It could be sped up by generating patterns from the collections so that you reduce the number of times that you access the worksheet range.

Taking a step back, a faster way to do it would be to combine all the cells in a word document then copy the result back into K1.
I'm not very good with the word object model but I'll do some testing and this idea may give someone else the inspiration to help you.

 

[skyport]

Teeroy, I sure appreciate your help. The problem I encountered with the word doc idea is only one major issue. it won't join the cells without leaving a partial line of blank space at the end of each cell data.

Also, with your code, is there a way to do what I was asking above about the space/comma or periods?

 

[Peter_SSs]

Also, with your code, is there a way to do what I was asking above about the space/comma or periods?

Teeroy may have a different idea but I think the blue changes would be one way.

The red changes should speed the code a reasonable amount too.

Sub JoinThem3()
Dim i As Long
Dim j As Integer
Dim s As String
Dim cBold As New Collection
Dim cUnderline As New Collection

Const Delim As String = ","

Application.ScreenUpdating = False
With Range("K1")
    .ClearContents
    For i = 1 To Range("E" & Rows.Count).End(xlUp).Row
        s = s & Delim & Cells(i, "E").Value
        For j = 1 To Len(Cells(i, "E")) + Len(Delim)
            cBold.Add Cells(i, "E").Characters(Start:=j, Length:=1).Font.Bold
            cUnderline.Add Cells(i, "E").Characters(Start:=j, Length:=1).Font.Underline
        Next j
    Next i
    .Value = Mid(s, Len(Delim) + 1)
    For i = 1 To Len(.Cells(1, 1))
        .Characters(Start:=i, Length:=1).Font.Bold = cBold(i)
        .Characters(Start:=i, Length:=1).Font.Underline = cUnderline(i)
    Next i
End With
Application.ScreenUpdating = True
End Sub

 

[skyport]

Peter, I think that final input did the trick. I thank you both for the great help as always. Do you think when I am dealing with a few dozen cells, it will be much slower?

 

[Peter_SSs]

Do you think when I am dealing with a few dozen cells, it will be much slower?

For about 20 cells with 1,400+ characters, my (fairly old) machine took roughly 10-12 seconds without those ScreenUpdating lines and about 4-5 seconds with them.

 

[skyport]

That should do it. Thanks again for the great team effort. See you again soon.

 

[sergioMabres]

Hi Sky I think you may have an older version of the code I posted, the the line
j = j + Len(c) + 2

Just to be sure copy the VBA again to your book

Cheers
Sergio

 

[Peter_SSs]

Just to be sure copy the VBA again to your book

Sergio

Of course the code you posted aims to check & replicate many more font properties than skyport specifically asked for, but for some reason does not include the Bold property that was specifically mentioned by skyport.

To test that code against Teeroy's I removed all the properties except Underline and then added in Bold. I agree that it does the job, but for me it was much slower. For the exact same data I used to test and report in post #27 this code took 55+ seconds with ScreenUpdating on and still 20+ seconds with it off.

When I tried leaving all the font properties in the code, I gave up waiting after about 2 minutes.