Okay, you may find this version of the function more flexible to use. The first version I posted *requires* a contiguous range of cells; this version will work with multiple, non-contiguous ranges; for example, something like this...

=BigConcat("; ",A1:A10,B2:H2,J3:M9)

The only problem (if this is even a problem) is that the Delimiter is no longer optional and there is no default for it (and it has been moved to the first argument position in order to allow an unspecified number of ranges to be passed into the function as a comma delimit list). Okay, here is the function code...

Function BigConcat(Delimiter As String, ParamArray Data()) As String
Dim X As Long, Z As Long, IR As Range
For Z = LBound(Data) To UBound(Data)
For X = Data(Z)(1).Row To Data(Z)(1).Row + Data(Z).Rows.Count - 1
Set IR = Intersect(Data(Z), Rows(X))
If IR.Count = 1 Then
BigConcat = BigConcat & IR.Value & Delimiter
Else
BigConcat = BigConcat & Join(WorksheetFunction.Transpose( _
WorksheetFunction.Transpose(Intersect( _
Data(Z), Rows(X)))), Delimiter) & Delimiter
End If
Next
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