CODEWITHSUNDEEP
excelvba
Nick
Nick

Reputation: 347

Merge selected cells into one cell with comma

How i could merge cells in selected cells into one cells,the seleced cell number will vary it may be 5 cells or more but all cells will be continues like A1,A2,A3 etc. i already go through the article Combine multiple cells into one in excel with macro? but how i can use the above mentioned link answer for selected cells. I am using Excel 2007 so i expect it's compactible code.

Upvotes: 3

Views: 3804

Answers (3)

Naresh
Naresh

Reputation: 3034

For 1D or 2D ranges. Can be applied to range containing blank cells. We can also assign a shortcut key say Ctrl+Shift+M like the shortcut key in MS word to merge cells in a table is Alt+M

Caution .. Macro results cannot be undone. So, try this macro on a sample data first. However, results of this MergeCellsWithComma macro can be undone by the second procedure restoreMergedCells if run on the same merged range while value of Public variable: valRng is not changed.

Make sure rowDelimiter and cellDelimiter are same in both the procedures.

Public valRng As String
'______________________________________________________________

Sub MergeCellsWithComma()
If Selection.MergeCells = True Then Exit Sub
Application.DisplayAlerts = False

rowDelimiter = "}"
cellDelimiter = "|"

Dim rng As Range: Set rng = Selection
If rng Is Nothing Then Exit Sub
On Error GoTo 0
Dim cL As Range
valRng = ""
For i = 1 To rng.Rows.Count
    For Each cL In Application.Index(rng, i, 0)
    'If cL <> "" Then valRng = valRng & cL & cellDelimiter
    'We can remove this if condition and keep only
    valRng = valRng & cL & cellDelimiter
    'in case we want the resulting string to show blank cells as well.
    'It would be better to do so if 'the original data needs to be restored
    Next
    'Following is a separate delimiter for rows in the selection.
    'If not needed comment it
    valRng = Left(valRng, Len(valRng) - 1) & rowDelimiter
    ' instead of rowDelimiter one can use Chr(10) for line break
Next

valRng = Left(valRng, Len(valRng) - 1)
rng.MergeCells = True
Range(rng(1, 1).Address) = valRng
Application.DisplayAlerts = True

End Sub

Thought behind creating different delimiter for rows : if anything goes wrong we should be able to restore the merged cells using following procedure. If value of Public variable: valRng is changed before running this procedure then assign it with valRng = Selection.Cells(1, 1).Value.

Sub restoreMergedCells()
If Selection.MergeCells = False Then Exit Sub
rowDelimiter = "}"
cellDelimiter = "|"

Selection.MergeCells = False
Dim valRngRowsCells

'If value of Public variable: valRng is changed before running this procedure
valRng = Selection.Cells(1, 1).Value
valRngRows = Split(valRng, rowDelimiter)
' rowDelimiter or Chr(10) whatever applied in above macro
a = LBound(valRngRows): x = UBound(valRngRows)

If InStr(1, valRngRows(1), cellDelimiter, 1) > 0 Then
valRngRowsCells = Split(valRngRows(1), cellDelimiter)
b = LBound(valRngRowsCells): y = UBound(valRngRowsCells)

ReDim valRngRowsCells(0 To x, 0 To y)

For i = a To x
    For j = b To y
    valRngRowsCells(i, j) = Split(valRngRows(i), cellDelimiter)(j)
    Next
Next

'Range(Cells(1, 1), Cells(x + 1, y + 1)) = valRngRowsCells
Selection.Value = valRngRowsCells
Else
Selection.Value = Application.Transpose(valRngRows)
End If

End Sub

Upvotes: 0

Pankaj
Pankaj

Reputation: 11

Just try below code copy , paste and save it. |||||||||||

Required Data   Result     Code
1            1,2,3,4,5,    =darksider_con(A1:A5)
2       
3       
4       
5       

Function darksider_con(rng As range) As String
Dim cell As range
Dim result As String
For Each cell In rng
result = result & cell.Value & ","
Next
darksider_con = result
End Function

Upvotes: 1

Mike
Mike

Reputation: 821

I think this should do the trick. It basically uses a range variable to handle the selection. Then it fills an array with the values of the selected cells. CSV is variable containing your result.

Note that selectedCells is a variable here not some special function in Excel.

[This code worked in Excel 2002 - unsure on other versions.]

Dim selectedCells As Range
Dim rng As Range
Dim i As Integer
Dim values() As String
Dim CSV As String

' you may need some error handling here in case your selection
' isn't a range
Set selectedCells = Selection

ReDim values(selectedCells.Count - 1)

i = 0
For Each rng In selectedCells
  ' you may want some error handling here when populating the array
  values(i) = CStr(rng.Value)
  i = i + 1
Next rng

CSV = Join(values, ",")

Upvotes: 2

Related Questions