ConcatenateRange.vba 602 B

12345678910111213141516171819202122232425
  1. Function ConcatenateRange(ByVal cell_range As Range, _
  2. Optional ByVal seperator As String) As String
  3. Dim cell As Range
  4. Dim newString As String
  5. Dim cellArray As Variant
  6. Dim i As Long, j As Long
  7. cellArray = cell_range.value
  8. For i = 1 To UBound(cellArray, 1)
  9. For j = 1 To UBound(cellArray, 2)
  10. If Len(cellArray(i, j)) <> 0 Then
  11. newString = newString & (seperator & cellArray(i, j))
  12. End If
  13. Next
  14. Next
  15. If Len(newString) <> 0 Then
  16. newString = Right$(newString, (Len(newString) - Len(seperator)))
  17. End If
  18. ConcatenateRange = newString
  19. End Function