Merge contents of 2 Excel cells keeping character format intact (using VBA)

Question

As the title says: I try to merge the contents of 2 cells into a 3rd in Excel. There was a similar question here on SO, but those solutions do not keep the character format intact. For example, parts of the source cell contents are formatted bold and red, other parts are normal. When I merge them like

 Range("A3") = Range("A1") & Range("A2")

then any formatting of A1 and A2 is lost. What I need is a solution keeping the format intact. This is going to be part of a bigger VBA program, so I need a VBA solution, no formula, please. Excel version is 2002(XP).

Answer 1

I know of no way of doing this directly, but it would not be that hard to copy the ranges to the new range then loop through the interior properties of each character of the original ranges and copy the properties over. That should preserve colour, font style etc. for each character in the resulting range.

Home time beckons so I've not got time to whip up an example, but I'm sure others edit/answer if need be.

Answer 2

Doc, that is an interesting question. I was stumped myself but saw the value, so after some searching, here is what I found. From vbaexpress I got the basic understanding of in cell formatting, which I modified for your use below.

Sub Merge_Cells()
Dim iOS As Integer
Dim rngFrom1 As Range
Dim rngFrom2 As Range
Dim rngTo As Range
Dim lenFrom1 As Integer
Dim lenFrom2 As Integer

  Set rngFrom1 = Cells(1, 1)
  Set rngFrom2 = Cells(1, 2)
  Set rngTo = Cells(1, 3)
  lenFrom1 = rngFrom1.Characters.Count
  lenFrom2 = rngFrom2.Characters.Count

  rngTo.Value = rngFrom1.Text & rngFrom2.Text

  For iOS = 1 To lenFrom1
    With rngTo.Characters(iOS, 1).Font
      .Name = rngFrom1.Characters(iOS, 1).Font.Name
      .Bold = rngFrom1.Characters(iOS, 1).Font.Bold
      .Size = rngFrom1.Characters(iOS, 1).Font.Size
      .ColorIndex = rngFrom1.Characters(iOS, 1).Font.ColorIndex
    End With
  Next iOS
  For iOS = 1 To lenFrom2
    With rngTo.Characters(lenFrom1 + iOS, 1).Font
      .Name = rngFrom2.Characters(iOS, 1).Font.Name
      .Bold = rngFrom2.Characters(iOS, 1).Font.Bold
      .Size = rngFrom2.Characters(iOS, 1).Font.Size
      .ColorIndex = rngFrom2.Characters(iOS, 1).Font.ColorIndex
    End With
  Next iOS

End Sub

Just change out the 3 cells() with your specific cells. Maybe someone can find a cleaner way, but when I tested this, it worked as I understand you (and I) would like.
Hope this helps...

Answer 3

This is a bit of a shot in the dark, but if you could somehow write a macro that does the following, it seems like it would work beautifully:

1. Copy the entire range (e.g., A1:B200) to be merged.
2. Open a new Word document.
3. Paste (creates a table in Word).
4. Select the table.
5. Do "Convert To Text" with a blank delimiter (or whatever you want).
6. Copy the resulting text.
7. Paste into desired location (e.g., C1) in Excel.

I know you can write VBA macros for both Excel and Word, but I have doubts that you could control a Word document from Excel. You probably could write a C# console app that could open and control two documents, however.