CODEWITHSUNDEEP
excelconcatenationuser-defined-functionsvba
Melko
Melko

Reputation: 61

concatenate two columns row wise into single cell with new line

I am trying to evolve a user defined function named concall(Range1, Range2) that will let me first concatenate text in A1 and B1, then add new line, then concatenate A2 and B2 and so on depending upon the range.
Suppose, if data from A1 to A4 is
A
B
C
D

and from C1 to C4 is
Alpha
Beta
Gamma
Delta

If I enter concall(A1:A4, C1:C4) in any cell, it should display the following in that cell

A – Alpha
B – Beta
C – Gamma
D – Delta

Sample image file is Sample Image

Presently, I am manually concatenating the two columns and then using the following code for concatenating the data of multiple rows to one cell. 

Public Function rconc(data_range As Range)
Dim Cll As Range
For Each Cll In data_range
If Cll <> "" Then
rconc = rconc & vbCrLf & Cll
End If
Next Cll
If Len(rconc) > 0 Then rconc = Mid(rconc, 2)
End Function

Upvotes: 0

Views: 656

Answers (1)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

How about:

Public Function KonKat(r1 As Range, r2 As Range) As String
    Dim arr, brr, v As Range
    ReDim arr(1 To r1.Count)
    ReDim brr(1 To r2.Count)

    KonKat = ""
    i = 1
    For Each v In r1
        arr(i) = v.Value
        i = i + 1
    Next v
    i = 1
    For Each v In r2
        brr(i) = v.Value
        i = i + 1
    Next v

    KonKat = ""
    For i = 1 To r1.Count
        If KonKat = "" Then
            KonKat = arr(i) & "-" & brr(i)
        Else
            KonKat = KonKat & vbCrLf & arr(i) & "-" & brr(i)
        End If
    Next i

End Function

enter image description here

EDIT#1:

To avoid records in which either member of the pair is blank, replace the last For loop with:

For i = 1 To r1.Count
        If arr(i) <> "" And brr(i) <> "" Then
            If KonKat = "" Then
                KonKat = arr(i) & "-" & brr(i)
            Else
                KonKat = KonKat & vbCrLf & arr(i) & "-" & brr(i)
            End If
        End If
 Next i

Upvotes: 3

Related Questions