Concatinate the column values

[anoobkb]

My excel sheet has some values distributed in columns and the length of the column may be vary with data. There are some instances like a blank value will be populated instead of the real data. I am looking for a Macro where concatinate all the values in the column to first column. For example column 1 has the value a , c2 = b , c3 = c , c4 = <no value> , c5 = <no value> , c6 = f , c7=g , c8 = h. I wish to concatinate all the column values and store it in c1 with out loosing the space. C1 will have the following value after a script is being executed.
c1= abc fgh

There may be multiple rows in a sheet and would like to concatinate the columns for each row.
Can anyone help?

 

[VBABEGINER]

Lets try this -


<TABLE style="WIDTH: 526pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=701 x:str><COLGROUP><COL style="WIDTH: 526pt; mso-width-source: userset; mso-width-alt: 25636" width=701><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 526pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17 width=701>Sub Concatenate()</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17> Dim i As Long</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17> dim rangeC as Range
Application.ScreenUpdating = False
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17> rangeC = Range("C2").End(xlDown).Row
For i = 2 To rangeC
</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17> Cells(i, "C1") = "" & Cells(i, "rangeC")</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=17> Next</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>End Sub</TD></TR></TBODY></TABLE>

 

[Peter_SSs]

Welcome to the MrExcel board!

There seems to be a little confusion about rows and columns. c2, c3, c4 etc are cells in a column and you want to concatenate them. That is, you would be concatenating the rows in column C. You later say you want to conacenate the columns for each row which is the other way round. Can you please clarify exactly what it is you want?

 

[anoobkb]

I have multiple columns and multiple rows in a sheet. The script should concatinate all the column value to first column and the process should repeat for all the rows in a sheet. The sheet may be like this :-

c1 c2 c3 c4 c5 c6 c7
r1 1 2 2 5
r2 5 4
r3 1 2 3 4 6 3 1

Out put should be
c1 c2 c3 c4 c5 c6 c7
r1 122 5
r2 5 4
r3 1234631

 

[anoobkb]

The script provided by VBABeginner is throwing error.

Unfortunately I am not able to upoload images from my PC.

My requirement is concatinating all the column values into first column.
If there are any blank cells, then a blank space should be keep in between the data.

And there will be multiple records(rows) in a sheet and each rows have multiple columns.

 

[Ruddles]

Try:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Sub Concatenate()[/FONT]
 
[FONT=Fixedsys] Dim iLastRow As Long[/FONT]
[FONT=Fixedsys] Dim iLastColumn As Long[/FONT]
[FONT=Fixedsys] Dim iRow As Long[/FONT]
[FONT=Fixedsys] Dim iColumn As Long[/FONT]
 
[FONT=Fixedsys] iLastRow = Cells(Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Fixedsys] For iRow = 1 To iLastRow[/FONT]
[FONT=Fixedsys]   iLastColumn = Cells(iRow, Columns.Count).End(xlToLeft).Column[/FONT]
[FONT=Fixedsys]   Cells(iRow, "A") = "!" & Cells(iRow, "A")[/FONT]
[FONT=Fixedsys]   For iColumn = 2 To iLastColumn[/FONT]
[FONT=Fixedsys]     If IsEmpty(Cells(iRow, iColumn)) Then[/FONT]
[FONT=Fixedsys]       Cells(iRow, "A") = Cells(iRow, "A") & " "[/FONT]
[FONT=Fixedsys]     Else[/FONT]
[FONT=Fixedsys]       Cells(iRow, "A") = Cells(iRow, "A") & Cells(iRow, iColumn)[/FONT]
[FONT=Fixedsys]     End If[/FONT]
[FONT=Fixedsys]   Next iColumn[/FONT]
[FONT=Fixedsys]   Cells(iRow, "A") = Replace(Cells(iRow, "A"), "!", "", 1)[/FONT]
[FONT=Fixedsys] Next iRow[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

 

[anoobkb]

Re: Concatenate the column values

Perfect !! Its working as indeed
Thanks

 

[Ruddles]

Indeed is as good as I could have hoped for! Thanks for the feedback!

 

[Peter_SSs]

Re: Concatenate the column values

Is it possible that the first column (or more) in a row can be blank? If so, should there be a space(s) at the left of the result string?

Here's another approach to consider.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Concat2()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b, c<br>    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, cls <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    cls = ActiveSheet.UsedRange.Columns.Count + 1<br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange.Resize(, cls)<br>        a = .Value<br>        .SpecialCells(xlCellTypeBlanks).Value = " "<br>        b = .Value<br>        <SPAN style="color:#00007F">ReDim</SPAN> c(1 <SPAN style="color:#00007F">To</SPAN> .Rows.Count, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>        <SPAN style="color:#00007F">For</SPAN> r = 1 <SPAN style="color:#00007F">To</SPAN> .Rows.Count<br>            c(r, 1) = RTrim(Join(Application.Index(b, r, 0), ""))<br>        <SPAN style="color:#00007F">Next</SPAN> r<br>        .Value = a<br>        .Resize(, 1).Value = c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[anoobkb]

Peter, Thanks for the post. I did not see any such kind of data in the sheet till now but chances are there.