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
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 ".".