Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Merge contents of selected cells in one cell

Posted on 2012-03-20
2
Medium Priority
?
281 Views
Last Modified: 2012-03-20
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
0
Comment
Question by:AndreasHermle
2 Comments
 
LVL 3

Accepted Solution

by:
Frank White earned 2000 total points
ID: 37743121
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
 

Author Closing Comment

by:AndreasHermle
ID: 37743253
Hi DaFranker,

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

This forum deserves its name.

Thank you very much. Regards,  Andreas
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question