Combine Cell text

[Maheshp]

I use below code to combine cell text in Range to single cell, but it dont ignore blank cells i want output as 1;3 in cell B2, i will appreciate any help on this

<TABLE style="WIDTH: 111pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=148><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Number</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=84>Output</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>1;;3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

Sub combinecell()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LR
comb = comb & Cells(i, 1) & ";"
Next i
comb = Left(comb, Len(comb) - 1)
Range("B2") = comb
End Sub

 

[VoG]

Try

Sub combinecell()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LR
    If Cells(i, 1).Value <> "" Then comb = comb & Cells(i, 1) & ";"
Next i
Range("B2").Value = Left(comb, Len(comb) - 1)
End Sub

 

[Maheshp]

<marquee> Sir VoG </marquee>
Thank u so much !
Appreciate your help & time !

 

[Maheshp]

Sir VoG
need one more help
sometimes i get #VALUE! in cell "B2", if there is no value in 1st column
in Excel 2003 which function i can use ? i think iferror function is not there.
i want to use Iferror function or any other, if error then cell value will be blank
Range("G6").Value =Application.WorksheetFunction.IfError(Left(comb, Len(comb) - 1))
not working

 

[VoG]

Maybe this

Sub combinecell()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LR
    If Cells(i, 1).Value <> "" Then comb = comb & Cells(i, 1) & ";"
Next i
If Len(comb) > 1 Then Range("B2").Value = Left(comb, Len(comb) - 1)
End Sub

 

[Maheshp]

Thank u so much ! its work perfect
but curious to know how to use Iferror function in formula in vb
does it same for excel 2003 or 2007

 

[VoG]

IFERROR is only available in Excel 2007+.

It is better to anticipate errors and code in VBA appropriately. Presumably you have a set of formulas which are all returning "" otherwise I don't understand the issue.

 

[Maheshp]

Thank u so much for info!