Concatenation of cells

[abhimaan]

Hi,

I am looking for a VB script to concatenate 3 columns as below:

A B C
abhimanyu Nagpal ECE

The output i want in A1 would be a single string with comma in the end of value from column C. Cell B1 and C1 values get deleted.

A B C
abhimanyu Nagpal ECE,

The number of such rows are not fixed, so need a loop to carry this concatenation operation until blank value in the cell is encountered.

thanks,
abhimanyu

 

[JoeMo]

Assuming your data starts in A1:

Sub ABC()
Dim rng As Range, lRw As Long, vA

lRw = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A1", "A" & lRw)
Application.ScreenUpdating = False
For Each c In rng
    If Not IsEmpty(c) Then
        vA = Join(Array(c.Value, c.Offset(0, 1).Value, c.Offset(0, 2).Value))
        c.Value = vA & ","
        c.Offset(0, 1).Resize(1, 2).ClearContents
        vA = ""
    End If
Next c
Range("A1").EntireColumn.AutoFit
End Sub

 

[abhimaan]

Joe,

It's working exactly the way I wanted...I do not have the original excel sheet with me right now where I need to run this macro. I hope it works fine there too.

Thanks a tons for your quick reply. You made it easy for me.

Thanks again !
Abhimanyu

 

[taurean]

You can skip looping if you want to. Assuming Column D is empty

Sub SkipLoop()
With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Offset(, 3).FormulaR1C1 = "=CONCATENATE(RC[-3],"" "",RC[-2],"" "",RC[-1],"","")"
    .Value = .Offset(, 3).Value
    .Offset(, 1).Resize(, 3).ClearContents
    .EntireColumn.AutoFit
End With
End Sub