Concatenate problem

[Craig1]

Hi guys,
I obtained some code from your good selves a while ago about a concatenate problem I came across.
Basically what i wanted was no double comma's when concatenating multiple cells and one in the middle was empty.
The code below works like a treat except for one little issue, if the first cell is empty I still get that darned comma, I have tried quite a few ways to solve this and all to no avail.

Function myConCat(S As Range) As String
Dim cell As Range
Dim con As String
Dim I As Integer
I = 1
For Each cell In S
If I = 1 Then
con = cell
ElseIf cell <> "" Then
con = con & ", " & cell
End If
I = I + 1
Next cell
myConCat = con
End Function

Any help would be appreciated.

Thanks Craig.

 

[Trebor76]

Hi Craig,

See how this goes:

Option Explicit
Function myConCat(S As Range) As String
    
    Dim cell As Range
    Dim con As String
    Dim I As Integer
    
    I = 1
    
    For Each cell In S
        If I = 1 Then
            con = cell
        ElseIf cell <> "" Then
            If con = "" Then
                con = con & cell
            Else
                con = con & ", " & cell
            End If
        End If
        I = I + 1
    Next cell
    
    myConCat = con
    
End Function

Regards,

Robert

 

[Craig1]

Thanks for that Robert, it works fantastic. Thanks for the continued help and support.

I see what you did now, I was trying to get to that, I had the right idea about having another IF statement but just couldn't get there.

Thanks again.

Craig.

 

[Trebor76]

No problem. I'm glad we were able to provide you with a solution