Merge contents of selected cells in one cell

Cells_To_Merge      Result
17                                17,15,20
15      
20      
      
Dear Experts,       
      
I would like to select any number of cells in a column.      
The macro that is activated is to merge the contents of the selected cells in one cell, whereby the entries are to be separated by a comma.       
The merged content is to be filled into the cell that is adjacent to the first cell of the selection      
      
I have attached a sample file with detailed instructions and an example

Help is much appreciated. Thank you very much in advance.

Regards, Andreas      
Merging-Cells.xls
Andreas HermleTeam leaderAsked:
Who is Participating?
 
Frank WhiteConnect With a Mentor Commented:
This code will make a string out of the text shown by the selected range and put the result with comma separators in the cell immediately to the right of the top-left cell in the selection.

Note that this will produce unwanted results if you select a range with more than one column and/or more than one area.

Option Explicit

Sub MergeMacro()
    Dim rMyRange As Range
    Dim rCurCell As Range
    Dim sResult As String
    Dim iCount As Integer
    Set rMyRange = ActiveWindow.RangeSelection
    iCount = 1

    For Each rCurCell In rMyRange
        If rCurCell.Text <> "" Then
            If iCount > 1 Then sResult = sResult & ","
            sResult = sResult & rCurCell.Text
            iCount = iCount + 1
        End If
    Next
    
    rMyRange.Offset(0, 1).Resize(1, 1).Value = sResult
    
End Sub

Open in new window


As it is, the macro must be run manually. I'm assuming you know how to create a VBA module and run this as a macro, but if you need help with that just say so.
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi DaFranker,

great job. Wonderful!  Prompt support and excise, sophisticated code.

This forum deserves its name.

Thank you very much. Regards,  Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.