View Full Version : Solved: Remove the comma of the first cell
parscon
09-16-2012, 06:56 AM
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
09-16-2012, 07:00 AM
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
09-16-2012, 07:07 AM
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
09-16-2012, 07:33 AM
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
09-16-2012, 07:43 AM
Thank you very much for your good work .
shrivallabha
09-16-2012, 07:56 AM
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
09-16-2012, 07:57 AM
Thank you Good Man , Good Job .
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.