combine values from multiple columns into a single cell in column

[parscon]

I need a VBA code that can combine values from multiple columns into a single column that mean if i have a data on column A2-A3-A4-A30-AA-AB... combine all of them into A1 i have this VBA code but it is not work on my data, because the column A is empty . i attached a sample file enclosed this post.

Thank you for your help




Sub Comb()
Dim i As Long
For i = 1 To Range("A" & 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

[p45cal]

I'm not 100% sure what you mean.
Try changing:
'For i = 1 To Range("A" & Rows.Count).End(xlUp).Row Step 1
to:
For i = 1 To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

[parscon]

just u I need all data on row A combine to A1 and above code will do it but it if column A is empty it will not work.

[p45cal]

It worked here when I tried it on your data after making the change suggested in messgae#2.

[mancubus]

just u I need all data on row A combine to A1 and above code will do it but it if column A is empty it will not work.

actually it works. but if Column A is blank that means Range("A" & Rows.Count).End(xlUp).Row is 1.

so the For Next Loops only once.

your worksheet contains data from Column B to Column AA, from Row 1 to Row 2718. clearly define what you really want. if you want to concatenate cell values from col B to col AA in col A in the same row, follow p45cal's solution.

[mancubus]

there are blank rows in your table.



Sub Combine_Cells_ColB_thru_ColAA_in_ColumnA()


Dim LastRow As Long, LastCol As Long
Dim i As Long, j As Long, Calc As Long

With Application
.ScreenUpdating = False
.DisplayAlerts = False
Calc = .Calculation
.Calculation = xlCalculationManual
End With

With ActiveSheet
.Columns("A").Clear
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
LastCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
For i = 1 To LastRow
For j = 2 To LastCol
If .Cells(i, j).Value <> "" Then
.Cells(i, 1).Value = Mid(.Cells(i, 1).Value & ", " & .Cells(i, j).Value, 3)
End If
Next j
Next i
End With


With Application
.Calculation = Calc
End With
End Sub

[parscon]

Thank you very much for your help

[mancubus]

you are welcome.