macro to merge values from one column into one cell and retain source formatting.

I need to compile data from multiple cells from one column into a single cell. Some of the source cells are formatted with as underlined text. I need to retain the formatting after the cells are merged into the destination cell. I found VBA to combine two ranges of cells and retain the source formatting, and it was more than I needed. How can I use just one range of cells and combine them? I don't want spaces or commas between the output values (I'll provide them when needed).

Example:

Source:

A1= "It is going to cost "
A2= "$1000.00" (A2 is formatted to underline value)

Destination: (desired result)

B2= "It is going to cost $1000.00" (A2 value is still underlined)


My plan is to custom build each cell column A so that a macro can string the results together in one cell formatted as a paragraph with source formatting retained. I could use the concatenate function except it loses the source character formatting.

Please help gently...very new to VBA

PHP Code: 

Option Explicit
Sub ScottTaylor()
 Dim Rng As Range, Cls As Range
 
 Set Rng = Columns("A:A").SpecialCells(xlCellTypeConstants, 1)
 For Each Cls In Rng
    Cls.Select
    Selection.AutoFill Destination:=Range(Cls, Cls.Offset(, 1)), Type:=xlFillDefault
    Cls.Offset(, 1).Value = Cls.Offset(-1).Value & CStr(Cls.Value)
 Next Cls
End Sub 


I can't get the code to do anything. I need instructions on how to implement it once the macro is in a module.

Originally Posted by Sa DQ

PHP Code: 

Option Explicit
Sub ScottTaylor()
 Dim Rng As Range, Cls As Range
 
 Set Rng = Columns("A:A").SpecialCells(xlCellTypeConstants, 1)
 For Each Cls In Rng
    Cls.Select
    Selection.AutoFill Destination:=Range(Cls, Cls.Offset(, 1)), Type:=xlFillDefault
    Cls.Offset(, 1).Value = Cls.Offset(-1).Value & CStr(Cls.Value)
 Next Cls
End Sub 


I have no VBA experience, so I need to have step by step implementation instructions. How do I specify the souce cells I want to combine and how do I specify the destination cell? Thanks so much for the help

Stan,

Thanks for the code. Unfortunately it appears to be the same code I already got. I need to be able to specify which worksheet within the workbook to run the macro and I also need to know how to specify the source cells and the destination cell. I need to understand the syntax of the macro details in order to change or customize the macro. My workbook has more than 15 worksheets and they all have conditional formulas scattered throughout. The particular worksheet that needs this macro is a document that must ultimately be printed as a pdf document with precise language, spacing, fonts, etc. to duplicate another document. Because the text of the document must be justified and comes from several of the other worksheets within the workbook I found that a single column should contain all the separate pre-formatted sections of the paragraph(destination cell).

I haven't finished composing the sheet that I want to use the macro with, but I have attached a much simplified version of a workbook that may make things more clear.

Thank you very much for your help.release-trial (Autosaved).xlsm

This should be just enough for your need.

Please Login or Register  to view this content.

release-trial (Autosaved) With Code.xlsmrelease-trial (Autosaved).xlsm

Stan,

Its almost what I need. However, if I change the location of the starting cell and the corresponding range references the underlining is off by one space.

If I move the starting cell of the source range down to a higher cell number, i.e. A117, the cells change all the formatting to odd places, i.e, italics and underlining starts in new spots.

I need to lock the formatting to the exact location of the source formatting. Cell A1 will not ultimately be where I start the range...it will be more like A117 and continue on through A185 or so.

Because the output must perfectly match a benchmark document I also need to control the spacing by putting spaces in the source cells and have the macro note the spacing without adding its ownspacing. I observed that in the output the parenthesis were separated by a space between the underlined 12 text. I need to be able to bring the parenthesis right next to the number without underlining the parenthesis. That's why I suggest I need to control the spacing from the source cell side.

Thanks for all the amazing help. I'm much farther along than I was two days ago.

Who are you talking to?

Change to
1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

Sorry,

Got confused with all the great help, I wasn't sure who sent the help. I'm going to try the change suggested. Thanks!

WOW....I'm just about there! Thanks again. One last issue....can we lose the autospacing between words and let me dictate the spacing in the source cells?

Sure.

Please Login or Register  to view this content.

Fantastic! Thank you so much Jindon! Where do I meet you to buy dinner?

Thanks for the dinner and don't forget to mark the thread as "Solved".

Jindon....One more heroic gesture? I need the macro to only run on the sheet named "Release" I have over 20 sheets in the workbook and I can't have it running on other sheets.

1) Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

2) Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

You are scary smart.

I've finally got the worksheet partially built. The code provided is working great, but I need (A11) to automatically adjust row height to fit the text. Is there a place to insert a bit of code in this macro?

Option Explicit

Sub Release_Combine_Text()
Dim rng As Range, r As Range, txt As String, Temp As Long
With Sheets("Release")
Set rng = Range("A117", Range("A" & Rows.Count).End(xlUp))
End With
For Each r In rng
txt = txt & r.Text
Next
With rng.Parent.Range("a11")
.Value = txt
For Each r In rng
With .Characters(Temp + 1, Len(r.Text)).Font
.FontStyle = r.Font.FontStyle
.Size = r.Font.Size
.Name = r.Font.Name
.Color = r.Font.Color
.Strikethrough = r.Font.Strikethrough
.Subscript = r.Font.Subscript
.Superscript = r.Font.Superscript
.Underline = r.Font.Underline
End With
Temp = Temp + Len(r.Text)
Next
End With
End Sub

I've finally got the worksheet partially built. The code provided is working great, but I need (A11) to automatically adjust row height to fit the text. Is there a place to insert a bit of code in this macro?

Option Explicit

Sub Release_Combine_Text()
Dim rng As Range, r As Range, txt As String, Temp As Long
With Sheets("Release")
Set rng = Range("A117", Range("A" & Rows.Count).End(xlUp))
End With
For Each r In rng
txt = txt & r.Text
Next
With rng.Parent.Range("a11")
.Value = txt
For Each r In rng
With .Characters(Temp + 1, Len(r.Text)).Font
.FontStyle = r.Font.FontStyle
.Size = r.Font.Size
.Name = r.Font.Name
.Color = r.Font.Color
.Strikethrough = r.Font.Strikethrough
.Subscript = r.Font.Subscript
.Superscript = r.Font.Superscript
.Underline = r.Font.Underline
End With
Temp = Temp + Len(r.Text)
Next
End With
End Sub

You need to edit you post and wrap the code with code tag first.
It's a "MUST" rules here.
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

I've finally got the worksheet partially built. The code provided is working great, but I need (A11) to automatically adjust row height to fit the text. Is there a place to insert a bit of code in this macro?

Please Login or Register  to view this content.