CODEWITHSUNDEEP
excelvba
CCID
CCID

Reputation: 1448

Combine multiple cells into one in excel with macro?

I have a similar question to this one:

Merge the contents of 2 cells into another 3rd cell using VBA in Excel

But I want to combine a range of cells within a column, eg A2:A50. Sometimes I have over 300 cells to be combined into one. Values are text. Is there any way to modify this macro so that it works on a range instead of just two cells?

Thanks!

Upvotes: 7

Views: 77146

Answers (4)

Michael Stahl
Michael Stahl

Reputation: 248

Here is a macro developed circa 2006. I still use it today!

Usage:

  • Select the cells you want to merge (the marco assumes they are all in the same column, one below the other)
  • Run the macro (a good idea is to assign it a short-cut key via Excel's Developer->Code->Macros-> select the macro -> Options)
Sub MergeCells()
'
Dim myString As String
Dim myUnion As Range
    
   Count = Selection.Count
    
   myValue = Selection
    
   myrow = Selection.Row
   mycol = Selection.Column
   myString = ""
   
   For Index = 1 To Count
      If Index > 1 Then myString = myString & Chr(10) & myValue(Index, 1) Else myString = myValue(Index, 1)
   Next Index

  Selection.ClearContents
  Cells(myrow, mycol) = myString
  
  
' Uncomment this loop if you want to delete the rows that are below the top cell (the rows with the cells whose content was concatenated to the top cell)
' For Index = 1 To Count - 1
'    Rows(myrow + 1).Delete
' Next Index
    
 Cells(myrow, mycol).Select
       
End Sub

Upvotes: 0

HotSauceCoconuts
HotSauceCoconuts

Reputation: 321

Just to add to Mike's solution, if you want to get your range from a variable instead of a defined range (I had trouble with the syntax):

Sub MyMacro()

dim myVar As Range

    MsgBox ConcatinateAllCellValuesInRange(myVar)

End Sub

Upvotes: 0

Paul McMahon
Paul McMahon

Reputation: 41

Try the following macro, not very elegant in that it doesn't do any error checking etc but works. Assign the macro to a button, click in a cell, click the macro button, highlight the desired (source) range to merge using your mouse (will autofill in range in the input box in the dialogue box), click ok, highlight the destination cell (will autofill the input box in the next dialogue box) click ok, all cells will be merged with a single space character into the destination cell, which can be in the original source range). Up to you to delete the superfluous cells manually. Workks with both rows and columns but not blocks.

Sub JoinCells()

Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge",    Type:=8)
xSource = 0
xSource = xJoinRange.Rows.Count
xType = "rows"
If xSource = 1 Then
    xSource = xJoinRange.Columns.Count
    xType = "columns"
End If
Set xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)
If xType = "rows" Then
    temp = xJoinRange.Rows(1).Value
    For i = 2 To xSource
        temp = temp & " " & xJoinRange.Rows(i).Value
    Next i
Else
    temp = xJoinRange.Columns(1).Value
    For i = 2 To xSource
        temp = temp & " " & xJoinRange.Columns(i).Value
    Next i
End If

xDestination.Value = temp

End Sub

Upvotes: 4

Mike Rosenblum
Mike Rosenblum

Reputation: 12157

Based on the thread you are citing, I guess you wish to return the concatination of all the values held by the cells, interpreting all the values as strings?

For that, you could use a VBA macro that looks like this:

Function ConcatinateAllCellValuesInRange(sourceRange As Excel.Range) As String
    Dim finalValue As String

    Dim cell As Excel.Range

    For Each cell In sourceRange.Cells
        finalValue = finalValue + CStr(cell.Value)
    Next cell

    ConcatinateAllCellValuesInRange = finalValue
End Function

As an example, you could call it like this:

Sub MyMacro()
    MsgBox ConcatinateAllCellValuesInRange([A1:C3])
End Sub

Is this what you were looking for?

Mike

Upvotes: 5

Related Questions