Join is a VB function that takes a one-dimensional array of Strings (1st
argument) and concatenates its elements together using whatever text you
specify as the delimiter (2nd argument). The Range property returns an
array; but, unfortunately, that array is a two-dimensional one which the
Join function cannot use. As for the worksheet Transpose function... I'm not
entirely sure why it actually works, but it does, so I use it.<g All I know
is that a **vertical** range of cells, when TRANSPOSE'd, becomes a
one-dimensional array, but when a **horizontal** range of cells is
TRANSPOSE'd, it remains a two-dimensional array. That is why your horizontal
range had to be TRANSPOSE'd twice... the first time to make it a vertical
(two-dimensional) array and the second time to make it a horizontal
one-dimensional array.

--
Rick (MVP - Excel)


"Benjamin" wrote in message
...
By the way Rick, Really awesome and short code!
Can you explain or give me link, to help me understand
Join and Transpose. Works like a charm! Thanks again.

"Rick Rothstein" wrote:

Here is a non-looping solution for you to try...

Sub ConCat()
With WorksheetFunction
ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _
ActiveCell.Row, ActiveCell.Offset(0, -1)))), ", ")
End With
End Sub

Note that I use a comma-space as a delimiter between the cell values (see
end of the long code line)... you can change this as necessary.

--
Rick (MVP - Excel)


"Benjamin" wrote in message
...
need code to concantenate all cells in row left of current active cell.
Say I click cell h2, I want cells a, b, c, d, e, f, g, to populate cell
h2.
I want to use this as a button on my excel toolbar. If I can create a
macro
for it then it would be really handy. As sometimes I'm concantenating
alot
of
data.

Is there a quick way to do that in vba?

.