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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Hour[/TD]
[TD]Min[/TD]
[TD]Sec[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]09[/TD]
[TD]53[/TD]
[TD]23[/TD]
[TD]9'53'23[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]42[/TD]
[TD]06[/TD]
[TD]10'42'6[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]00[/TD]
[TD]45[/TD]
[TD]16'0'45[/TD]
[/TR]
</tbody>[/TABLE]

DESIRED
[TABLE="width: 500"]
<tbody>[TR]
[TD]Hour[/TD]
[TD]Min[/TD]
[TD]Sec[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]09[/TD]
[TD]53[/TD]
[TD]23[/TD]
[TD]09'53'23[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]42[/TD]
[TD]06[/TD]
[TD]10'42'06[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]00[/TD]
[TD]45[/TD]
[TD]16'00'45[/TD]
[/TR]
</tbody>[/TABLE]


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!