XLA routines: EE_Concatenate

Nick's picture
EE_Concatenate concatenates a range like the CONCATENATE Excel function should work
Function EE_Concatenate(rng As range, strDelimiter As String) As String
'-concatenates the cells in the range
'Returns string.. Actually, add 2nd parameter called Delimiter..add cols then rows.
    Dim arr
    Dim iRow As Integer
    Dim iCol As Integer
    Dim strConc As String
'http://excelexperts.com/xla-routines-eeConcatenate    for updates on this function

    If rng.Cells.Count = 1 Then
        EE_Concatenate = rng.value
        arr = rng
        For iRow = LBound(arr, 1) To UBound(arr, 1)
            For iCol = LBound(arr, 2) To UBound(arr, 2)
                strConc = strConc & strDelimiter & arr(iRow, iCol)
            Next iCol
        Next iRow
        strConc = Trim(Mid(Trim(strConc), 2))
        EE_Concatenate = strConc
    End If
End Function