Trouble Merging Cells

[colbertja]

I need some assistance in combining multiple cells into one and keeping the formatting the same. I currently have code that will combine the columns but it losses the leading zero on numbers less than 10. For instance I gave some examples of the current output.

CURRENT

Hour	Min	Sec	Result
09	53	23	9'53'23
10	42	06	10'42'6
16	00	45	16'0'45

<tbody>
</tbody>

DESIRED

Hour	Min	Sec	Result
09	53	23	09'53'23
10	42	06	10'42'06
16	00	45	16'00'45

<tbody>
</tbody>

Below is a copy of the current code that I am using that will combine the columns it just will not continue the leading zero. I am pretty sure the issue is when I do the actual combining I am using "cell.Value" and it is not keeping the values. I want to either find a way to treat the range of cells as a string or keep the number formatting when I combine the three cells.


Function CombineColumn(myRange As Range)


Dim cell As Range
For Each cell In myRange
If Len(cell) > 0 Then
CombineColumn = CombineColumn & cell.Value & "'"
End If
Next cell

If Len(CombineColumn) > 0 Then
CombineColumn = Left(CombineColumn, Len(CombineColumn) - 1)
Else
CombineColumn = ""
End If

End Function


-Thanks in advance

 

[Norie]

Change Cell.Value to Cell.Text.

 

[colbertja]

Norie, that worked perfect! I thought it was something that simple but couldn't find it anywhere.

Thanks!