Solved: Remove the comma of the first cell

[parscon]

I use ths VBA code for Combine data from row to one cell and when i have a blank cell in A column add "," comma in the first of Combine data ,how can add funtion to remove it with this VBA code ?

Thank you for your help



Sub Comb()
Dim i As Long
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 1
For x = 2 To 256
If Cells(i, x).Value = "" Then
Else
Cells(i, 1).Value = Cells(i, 1).Value & ", " & Cells(i, x).Value
End If
Next x
Next i
End Sub

[JKwan]

According to your code, it should work, however, you may have a space in the cell.... So, try this:

Sub Comb()
Dim i As Long
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 1
For x = 2 To 256
If Trim(Cells(i, x).Value) <> "" Then
Cells(i, 1).Value = Cells(i, 1).Value & ", " & Cells(i, x).Value
End If
Next x
Next i
End Sub

[parscon]

Than you for your reply ,

you do not underestand what i need ,

when i use this code if Cell A is empty after combine add "," now i need remove this comma .
please check the attachment you will see my problem after run VBA.

[shrivallabha]

Modify your code like this:
Sub Comb()
Dim i As Long
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 1
For x = 2 To 256
If Trim(Cells(i, x).Value) <> "" Then
'Add condition here for checking if first cell is blank:
If Cells(i, 1).Value <> "" Then
Cells(i, 1).Value = Cells(i, 1).Value & ", " & Cells(i, x).Value
Else
Cells(i, 1).Value = Cells(i, x).Value
End If
End If
Next x
Next i
End Sub

[parscon]

Thank you very much for your good work .

[shrivallabha]

You can also use snb style coding to do this. See:
Sub Comb()
Dim i As Long
For i = 1 To Range("B" & Rows.Count).End(xlUp).Row Step 1
Range("A" & i).Value = Join(Filter(Evaluate("IF(A" & i & ":IV" & i & "<>"""",A" & i & ":IV" & i & ",""~"")"), "~", False), ",")
Next i
End Sub

[parscon]

Thank you Good Man , Good Job .

[snb]

or
Sub snb()
sn = Cells(1, 2).CurrentRegion
For j = 1 To UBound(sn)
c01 = c01 & "_" & Join(Application.Index(sn, j), "_")
Next
Cells(1) = Replace(Mid(c01, 2), "__", "_")
End Sub