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?

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


"Benjamin" wrote:

> 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?

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.

"Benjamin" wrote:

> 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?

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

Sorry about that.

"Gleam" wrote:

> 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.
>
> "Benjamin" wrote:
>
> > 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?

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" <(E-Mail Removed)> wrote in message
news:6F840EF1-7781-4D76-A8A4-(E-Mail Removed)...
> 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?

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" <(E-Mail Removed)> wrote in message
> news:6F840EF1-7781-4D76-A8A4-(E-Mail Removed)...
> > 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?
>
> .
>

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

"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" <(E-Mail Removed)> wrote in message
> news:6F840EF1-7781-4D76-A8A4-(E-Mail Removed)...
> > 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?
>
> .
>

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.

"Benjamin" wrote:

> 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" <(E-Mail Removed)> wrote in message
> > news:6F840EF1-7781-4D76-A8A4-(E-Mail Removed)...
> > > 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?
> >
> > .
> >

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" <(E-Mail Removed)> wrote in message
news:B6048A9B-3E8A-42AF-8513-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:6F840EF1-7781-4D76-A8A4-(E-Mail Removed)...
>> > 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?
>>
>> .
>>