Concatenate Range of Cells

[marcusmark]

Hi,
Is there a vba code to concatenate range of cells?
The range will start with CellA1 and can have up CellA1000 but this is not definite as it can be lower than that.
Concatenation need ";" as its separator.
Sample is when I have Apple in CellA1, Mango in CellA2, Orange in CellA3....the value in CellC1 will be Apple;Mango;Orange
Thanks!

 

[Michael M]

Maybe this UNTESTED

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
    For r = 1 To lr
        If Range("C1").Value = "" Then
            Range("C1").Value = Range("A" & r).Value + ";"
            Else
            Range("C1").Value = Range("C1").Value + Range("A" & r).Value + ";"
        End If
    Next r
End Sub

 

[marcusmark]

Hi Michael,
It got an error because of "+". I changed it to "&" instead and it now works perfectly fine. Thanks a lot!

 

[Rick Rothstein]

You can also do what you want without using a loop, all it takes is one line of code...

Sub ConcatenateA1ToLastValueInColumnA() [C1] = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ";") End Sub

 

[marcusmark]

Hi Rick, That was cool! :D

 

[Rick Rothstein]

Hi Rick, That was cool! :D

Thanks... I forgot to mention that it should also be faster as well.