Excel VBA - Has someone already created this

[Bob]

I am looking to create a VBA function that allows you to
select a range of cells and will concatinate the values
from that range into the next cell selected..

Any Ideas????

 

[Frank Kabel]

Hi
why not use a UDF for this. e.g.
MCONCAT
available in the free add-in Morefunc.xll
(http://longre.free.fr/english/)

 

[Ron Rosenfeld]

I am looking to create a VBA function that allows you to
select a range of cells and will concatinate the values
from that range into the next cell selected..

Any Ideas????

Perhaps this UDF will give you an idea. It requires entering the function into
the cell where you want to see the concatenated result.

The first entry is the number of spaces between each cell in the range.
Arguments 2..n can be individual cells or contiguous range references.

=====================
Function SetString(SpacesBetween As Integer, _
ParamArray rg() As Variant) As String
Dim c As Variant
Dim i As Long

For i = 0 To UBound(rg)
Select Case VarType(rg(i))
Case Is = vbArray + vbVariant
For Each c In rg(i)
SetString = SetString & Space(SpacesBetween) & c
Next
Case Is = vbString
SetString = SetString & Space(SpacesBetween) & rg(i)
End Select
Next i

SetString = Trim(SetString)

End Function
==============================


--ron

 

[Guest]

Thank you Both, I've run out of time today, will get back
to it on Monday and post my results... thanks again..
very much

 

[Ron Rosenfeld]

Hi
why not use a UDF for this. e.g.
MCONCAT
available in the free add-in Morefunc.xll
(http://longre.free.fr/english/)

Frank,

Can MCONCAT concatenate non-contiguous ranges?


--ron

 

[Frank Kabel]

Hi Ron

just testing it and no it does not

 

[Ron Rosenfeld]

Hi Ron

just testing it and no it does not

Thanks. I was not aware of MCONCAT and had written SetString in order to take
care of the issue of non-contiguous ranges. So I guess I did not waste my time
.


--ron