Try this

Function BigConcat(data As Range, Optional Delimiter As String) As String
Dim c, str As String, temp As String
Dim l As Integer, addr As String, msg
Dim r As Long, col As Integer
Dim Alert As String

If Len(Delimiter) = 0 Then
Delimiter = ", "
Else: Delimiter = Delimiter
End If
' Check that data will display in cell and ext if not
For Each c In data
' Results can be rubbish if a cell in data range is empry
If Not IsEmpty(c) Then
l = Len(c) + l
r = c.Row
col = c.Column
If l 1024 Then
addr = Cells(r, col - 1).Address
BigConcat = str
Alert = MsgBox("Value: " & temp & Chr(10) _
& "Cell " & Cells(r, col - 1).Address, , "Last cell Included in
Display!")
Exit Function
End If

If Len(str) = 0 Then
str = c
Else
str = str & Delimiter & c
End If

End If
Next c
BigConcat = Trim(str)
End Function


you can enter it =bigconcat(A2:A10) and it will use a comma between each cell.

or you can enter the delimiter of choice =BigConcat(A2:A10,";")

You could save the code in your Personal File then it will always be
available if you need it again.

If you have to create a personal file record a simple macro, save the file
as Personal. This is saved in Excel's Start up directory. You can see the
function under custom using the Function lists.

HTH
Peter

" wrote:

I had a string VBA code but I have lost it.

BAsicly I am trying to take several
rows of data combine them into one cell
with a ; and spce between each row once combines.
Pelase advise