Concantenate All cells in Row left of current active cell--

[Benjamin]

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?

 

[Benjamin]

Here's my code... need some help tweaking it to detect
the amount of cells to the left.

Sub ConcatColumns()

Do While ActiveCell <> "" 'Loops until the active cell is blank.

'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.

ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -3) & ActiveCell.Offset(0, -2) &
ActiveCell.Offset(0, -1) & ActiveCell.Offset(0, 0)

ActiveCell.Offset(1, 0).Select
Loop

End Sub

 

[Gleam]

Please try this:
Sub ConCat()
'
Dim i1 as long, str1 as string, started as boolean
'
if activecell.column>1 then
activecell.value=""
started=false
for i1=1 to activecell.column - 1
str1=trim(cells(activecell.row,1))
if len(str1)>0 then
if started then
activecell.value=activecell.value & ", " & str1
else
activecell.value=str1
end if
end if
next i1
end sub


It may easiest to go to tools/macro/macros and then select this macro and
then use options to set a letter so that the macro will run by typing say
Control+q.
Or with the macro window open you can step whrough with F8 or run with F5.

 

[Gleam]

Left a line out!
After "activecell.value=str1"
please add a line:
started = true

Sorry about that.

 

[Rick Rothstein]

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.

 

[Benjamin]

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.

 

[Benjamin]

What code to I remove to get rid of the "," delimiters?
Don't need them currently for my purposes.

 

[Benjamin]

Sub Macro2()
' Keyboard Shortcut: Ctrl+Shift+C
With WorksheetFunction
ActiveCell.Value = Join(.Transpose(.Transpose(Range("A" & _
ActiveCell.row, ActiveCell.Offset(0, -1)))), "")
End With
End Sub

Okay, I removed the delimiters.... with this code
so forget that last request... just hacked away at it until I got it working.

 

[Rick Rothstein]

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.