Podcast 1375 - better concatenate

[vaibhav106]

In recent podcast no 1375 nice simple code is shown to join the range of text.
but..
when the range have few blank cells then the separator repeats unnecessarily. How we can ask the code to skip the blank cell while joining the range?

 

[njimack]

I use the following function to concatenate ranges:

Function Concatenate_Range(myrange As Range, Optional myDelimiter As String)
Dim cell As Range

Application.Volatile

For Each cell In myrange
    If Len(cell.Value) > 0 Then
        Concatenate_Range = Concatenate_Range & cell & myDelimiter
    Else: Concatenate_Range = Concatenate_Range
    End If
Next cell

If Len(myDelimiter) > 0 Then Concatenate_Range = Left(Concatenate_Range, Len(Concatenate_Range) - Len(myDelimiter))

End Function

 

[vaibhav106]

thanks for such lightning reply..

I have used your idea in the existing code & updated that code as under:-


Function Join(RangeToJoin, Separator)
Join = ""
For Each cell In RangeToJoin
If Len(cell.Value) > 0 Then
Join = Join & cell.Value & Separator
Else: Join = Join
End If
Next cell
If Len(Join) > 0 Then
Join = Left(Join, Len(Join) - Len(Separator))
End If

End Function