/VBA/How to concatenate more than 40 lines in vba

How to concatenate more than 40 lines in vba

Function ConcatMe(Rng As Range) As String

Dim cl As Range

   ConcatMe = ""

   For Each cl In Rng
      ConcatMe = ConcatMe & cl.Text
   Next cl

End Function

Source: stackoverflow.com

How to concatenate more than 40 lines in vba

Function ConcatenateRange(ByVal cell_range As range, _
                    Optional ByVal seperator As String) As String

Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function

Source: stackoverflow.com

Similar codes

2 votes162 views1 answer
Vba array contains
VBA
0 vote170 views1 answer
Vlookup columns not next to each other
VBA
1 vote183 views1 answer
Excel vba last row of range
VBA
0 vote203 views1 answer
Excel vba unlock sheet without password
VBA
0 vote168 views1 answer
How to use VBA to add new record in MS Access?
VBA
0 vote174 views1 answer
Menus act like radio buttons vb.net
VBA
0 vote169 views1 answer
Type excel.application is not defined vb
VBA
3 votes184 views1 answer
Vba double quote
VBA
0 vote170 views1 answer
Vbnet access System.InvalidOperationException: 'Update requires a valid UpdateCommand when passed DataRow collection with modified rows.'
VBA
0 vote199 views1 answer
How to enter Vbox manage commands
VBA