I'm using a UDF from a previous post (http://www.ozgrid.com/forum/showthread.php?t=70327) that works as expected except when the concatenated cell range is blank. I'm admittedly over my head when it comes to VBA and I'm getting nowhere fast determining why the cell containing the UDF has a result of '0' when the range is blank -- I need the result to be blank.
Function ConcatenateRange(Parts As Range, Separator As String) ' Build a single string from a passed range with a
' passed separator between each value
Dim strTemp, sepTemp As String
Dim cel As Range
Dim cnt As Integer
strTemp = ""
For Each cel In Parts.Cells
If cel.Value = "" Or cel.Value = 0 Then
sepTemp = ""
Else
sepTemp = Separator
End If
If Len(strTemp) = 0 Then
strTemp = cel.Value
Else
strTemp = strTemp & sepTemp & cel.Value
End If
Next cel
ConcatenateRange = strTemp
End Function
Display More
Changing the Number Format of the cell doesn't seem to make any difference in the final result. Not that it should, I was just taking a stab in the dark this was somehow related to my results.
My separator as defined in the reference cell is a period, =ConcatenateRange(I6:L6,"."). I see no place in this Function to the best of my interpretation results in a '0', rather than a blank cell.
If I understand correctly, if the cel.Value = "" (empty) OR cel.Value = 0 then the result should be empty or the separator defined by ".".
I'm stumped