string contra VBA code
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
|