Two things... First, it's a UDF (User Defined Function), not a macro. Second, it seems I left out the default value for the Delimiter. Only the function declaration is changed, but I'm repeating the entire UDF to make it easier to copy/paste...

Function BigConcat(Data As Range, Optional Delimiter As String = ", ") As String
Dim X As Long, IR As Range
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
Set IR = Intersect(Data, Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data, Rows(X)))), Delimiter) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message ...
A minor oversight on my part. Use this macro instead...

Function BigConcat(Data As Range, Optional Delimiter As String) As String
Dim X As Long, IR As Range
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
Set IR = Intersect(Data, Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data, Rows(X)))), Delimiter) & Delimiter
End If
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function

--
Rick (MVP - Excel)


" wrote in message ...
maybe I am not saving the formula right.
Still get confused with VBA

I opened a brand to doc.
Copied and pasted the row that I want to considate in to one cell in colA
ok np
next went to the dev. tab
selected view code
right clicked the sheet
insert module
pasted the VBA code
ctrl S save, Saved as excel07 macro enab.
Close the VBA window

typed in b1 =bigconcat(highlighted the reange)
Then tried =bigconcat(a1:a1000,";")
hit enter and know I am geting #value in the cell

sorry guys,
"Rick Rothstein" wrote:

Here is my take on a macro to do what you want...

Function BigConcat(Data As Range, Optional Delimiter As String) As String
Dim X As Long
For X = Data(1).Row To Data(1).Row + Data.Rows.Count - 1
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect(Data, Rows(X)))), _
Delimiter) & Delimiter
Next
Do While InStr(BigConcat, Delimiter & Delimiter)
BigConcat = Replace(BigConcat, Delimiter & Delimiter, Delimiter)
Loop
BigConcat = Left(BigConcat, Len(BigConcat) - Len(Delimiter))
If InStr(BigConcat, Delimiter) = 1 Then
BigConcat = Mid(BigConcat, Len(Delimiter) + 1)
End If
End Function

--
Rick (MVP - Excel)


" wrote in message ...
hmmmmm
I got an error meesage that took me here
Alert = MsgBox("Value: " & temp & Chr(10) _
& "Cell " & Cells(r, col - 1).Address, , "Last cell Included in


=BigConcat(H3:H979,"; ") this is formula I enter. does the space matter
So basiclly I copied and then went to the dev. tab
view code
insert module
paste the formula
ctrl S (save)
close out the code page
and tried to plug that sucker in
The brackets () went to bold and everything after typing the message

"Billy Liddel" wrote:

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