Select range of cells and combine and merge that range into the first cell of the range and separate with line break

mfernandes 0
Viewed 27

I'm rusty as hell with VBA and am struggling with a basic script. What I need to do is have a script to select range of cells and combine and merge that range into the first cell of the range and separate with line break. For example, if data is as follows:

A1: Apple
A2: Ball
A3: Chair

I'd like to prompt the user to select a range (A1:A3 in this case), then concatenate the values in that range with a line break between each value, merge the same range that was selected, then paste the resulting concatenated values into that merged cell. So in this case, A1:A3 is merged into one cell with the following value:

Apple
Ball
Chair

vba excel
2 Answers

Something like this:

Option Explicit

Public Sub example()
    CombineAndMerge Range("A1:A3")
End Sub

Public Sub CombineAndMerge(ByVal MergeRange As Range)
    ' get content of cells and join with line breaks
    Dim Content As String
    Content = Join(Application.Transpose(MergeRange.Value), vbLf)
    
    ' merge
    MergeRange.Clear
    MergeRange.Merge
    
    ' write content with line breaks
    MergeRange.Cells(1, 1).Value = Content
End Sub

But I highly recommend to stay away from merged cells. They are evil and cause a lot of issues.

Pᴇʜ0

and something like this:

Sub foo()
    Dim cl As Range, result$
    For Each cl In Selection
        result = result & vbNewLine & cl.Value
    Next cl
    Application.DisplayAlerts = False
    Selection.Merge
    Selection.Value = result
    Application.DisplayAlerts = True
End Sub

And again, highly recommended to stay away from merged cells. They are evil and cause a lot of issues.

Vasily0
Related