0

I'm trying to add some code in the following Function in order to keep the hyperlinks from the data i concatenated together, but can't figure out how to do it.

Function ConcatenateRange(ByVal cell_range As Range, _
Optional ByVal seperator As String) As String
Dim cell As Range
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)) & vbLf
      End If
    Next
Next

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

ConcatenateRange = newString
End Function

Any tips is much appreaciated. Best Benjamin

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
Benjamin B.
  • 1
  • See: https://stackoverflow.com/questions/31457984/getting-an-excel-udf-to-return-a-hyperlink – Gary's Student Dec 02 '20 at 11:59
  • Please, edit your question and show us two examples. How three such cells looks and how the concatenated string must look. I cannot understand from your code what you are trying to do. Do you want putting all cells of the range in a string, one on top of the other? If yes, which to be the meaning of the separator, if each cell is placed on a new line? Are you intending to split the string on the separator and use the obtained array elements in building the hyperlink? And where, in your code, is the hyperlink you mentioned? – FaneDuru Dec 02 '20 at 12:14

0 Answers0