View Full Version : [SOLVED:] combine values from multiple columns into a single cell in column
parscon
11-28-2013, 11:41 AM
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
11-28-2013, 01:34 PM
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
11-28-2013, 01:40 PM
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
11-28-2013, 01:54 PM
It worked here when I tried it on your data after making the change suggested in messgae#2.
mancubus
11-28-2013, 02:18 PM
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
11-28-2013, 02:37 PM
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
11-29-2013, 01:06 AM
Thank you very much for your help
mancubus
11-29-2013, 01:51 AM
you are welcome.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.