concatenate row to last column with data

[dtopinka]

can someone proved me with vba code to concatenate all cells in a row to the last blank cell. Between each concatenation i would like a semicolon and blank space (; )


see the sheet provided in the link for my data example
Row 1 has one column with data.. Row 2 has 3 columns with data.. and so on..

I would like to concatenate all row columns with data into column A. Then repeat process to the final row.

Mr Excel - Google Drive

drive.google.com

 

[Joe4]

Cross-posted here: concatenate row to last blank cell - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[mumps]

Do you want to place the concatenated data for each row at the bottom of column A with the data for each row in one cell?

 

[dtopinka]

Do you want to place the concatenated data for each row at the bottom of column A with the data for each row in one cell?

No.. I would like the concatenated values to be in their corresponding row.. so row 1 will be row 1 column a, row 2 column a and so on..

or i can make a new column (column a) and all the concatenations can be in that column to corresponding row.

 

[Fluff]

How about

Sub dtopinka()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   Ary = Range("A1").CurrentRegion.Value2
   For r = 2 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(r, c) = "" Then Exit For
         Ary(r, 1) = Ary(r, 1) & "; " & Ary(r, c)
      Next c
   Next r
   Range("A1").Resize(UBound(Ary), 1).Value = Ary
End Sub

 

[dtopinka]

Cross-posted here: concatenate row to last blank cell - OzGrid Free Excel/VBA Help Forum

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

Hello Joe4. sorry about that.

 

[dtopinka]

How about

Sub dtopinka()
   Dim Ary As Variant
   Dim r As Long, c As Long
  
   Ary = Range("A1").CurrentRegion.Value2
   For r = 2 To UBound(Ary)
      For c = 2 To UBound(Ary, 2)
         If Ary(r, c) = "" Then Exit For
         Ary(r, 1) = Ary(r, 1) & "; " & Ary(r, c)
      Next c
   Next r
   Range("A1").Resize(UBound(Ary), 1).Value = Ary
End Sub

YES! Thank you!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Joe4]

Hello Joe4. sorry about that.

We typically do not ban members for first-time offenses (unless they are really egregious).
However, I do recommend reading through those rules, just so you know what they are and can avoid any other issues.

Thanks!