Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge Cells
Hi...There have been many posts about merging cells, but I haven't seen this
issue addressed. I want to take several cells in a column and merge them into one cell separated by return characters. Example: Input cells: - - - - - - | Bob | A1 - - - - - - | Joe | A2 - - - - - - | Ken | A3 - - - - - - Desired result after merge: - - - - - - | Bob | | Joe | A1 | Ken | - - - - - - | <blank | A2 - - - - - - | <blank | A3 - - - - - - I can create the result by manually entering returns via Option-Command-Return on my Mac and (IIRC) Ctrl-Return on my PC. I wrote the following macro to automate the merge. Unfortunately, the inserted CRs are not recognized in the merged cell (i.e., I get BobJoeKen in A1): Sub MergeCells() Dim rng As Range Dim cell As Range Dim OutStr As String Set rng = Intersect(Selection, ActiveSheet.UsedRange) If rng Is Nothing Then MsgBox "Select a range to be merged" GoTo done End If For Each cell In rng OutStr = OutStr & cell.Text & Chr(13) Next cell OutStr = Left(OutStr, Len(OutStr) - 1) ' Strip off final CR rng.ClearContents ' Clear original selected range rng.Cells(1).Value = OutStr ' Put new string into top cell done: End Sub Yet, if I manually enter A (CR) B (CR) C into a cell and run the following macro, I see that the three lines are indeed separated by two Chr(13). What am I missing? Sub GetSeparatorChar() ' Select one cell with "A," "B," and "C" in it separated by ' carriage returns (CR) Dim c As String Dim i As Integer For i = 1 To Len(Selection.Text) Debug.Print Asc(Mid(Selection.Text, i, 1)) Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge Cells
I don't think so.
"Steve Drenker" wrote: Hi...There have been many posts about merging cells, but I haven't seen this issue addressed. I want to take several cells in a column and merge them into one cell separated by return characters. Example: Input cells: - - - - - - | Bob | A1 - - - - - - | Joe | A2 - - - - - - | Ken | A3 - - - - - - Desired result after merge: - - - - - - | Bob | | Joe | A1 | Ken | - - - - - - | <blank | A2 - - - - - - | <blank | A3 - - - - - - I can create the result by manually entering returns via Option-Command-Return on my Mac and (IIRC) Ctrl-Return on my PC. I wrote the following macro to automate the merge. Unfortunately, the inserted CRs are not recognized in the merged cell (i.e., I get BobJoeKen in A1): Sub MergeCells() Dim rng As Range Dim cell As Range Dim OutStr As String Set rng = Intersect(Selection, ActiveSheet.UsedRange) If rng Is Nothing Then MsgBox "Select a range to be merged" GoTo done End If For Each cell In rng OutStr = OutStr & cell.Text & Chr(13) Next cell OutStr = Left(OutStr, Len(OutStr) - 1) ' Strip off final CR rng.ClearContents ' Clear original selected range rng.Cells(1).Value = OutStr ' Put new string into top cell done: End Sub Yet, if I manually enter A (CR) B (CR) C into a cell and run the following macro, I see that the three lines are indeed separated by two Chr(13). What am I missing? Sub GetSeparatorChar() ' Select one cell with "A," "B," and "C" in it separated by ' carriage returns (CR) Dim c As String Dim i As Integer For i = 1 To Len(Selection.Text) Debug.Print Asc(Mid(Selection.Text, i, 1)) Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically merge mulitiple cells to one cells | Excel Worksheet Functions | |||
how do I merge cells into one then delete the original cells? | Excel Worksheet Functions | |||
How can I have formatting options like merge cells ,Bold,active for the unlocked cells of the protected worksheet.Is it possible in excel? | Excel Programming | |||
How do I merge cells in Excel, like just 2 cells to make one big . | Excel Discussion (Misc queries) | |||
fill cells, merge cells | Excel Programming |