I wish to Concatenate a Range of values.
The range will always be a single row, from column A:K, however the
row number could vary.
So, the range will be highlighted / selected by the user.

How, using VBA, do i copy the selected range and Concatenate to a
single cell?
I would also like a "space" between each concatenated value.

Hi

Try someting like this:

Sub ConcatenateCells()
Dim TargetRange As Range
Dim NotFirst As Boolean
Dim MyString As String

Set TargetRange = Selection
If TargetRange.Rows.Count 1 Then Exit Sub
For Each cell In TargetRange
If NotFirst Then
MyString = MyString & " " & cell.Value
Else
MyString = cell.Value
NotFirst = True
End If
Next
Range("L" & TargetRange.Row) = MyString
End Sub

Regards,
Per


On 13 Sep., 23:38, Mik wrote:
I wish to Concatenate a Range of values.
The range will always be a single row, from column A:K, however the
row number could vary.
So, the range will be highlighted / selected by the user.

How, using VBA, do i copy the selected range and Concatenate to a
single cell?
I would also like a "space" between each concatenated value.

On Sun, 13 Sep 2009 14:38:07 -0700 (PDT), Mik
wrote:

I wish to Concatenate a Range of values.
The range will always be a single row, from column A:K, however the
row number could vary.
So, the range will be highlighted / selected by the user.

How, using VBA, do i copy the selected range and Concatenate to a
single cell?
I would also like a "space" between each concatenated value.


Try this macro:

Sub test()
result = ""
For Each c In Selection
result = result & " " & c.Value
Next c
Cells(1, "L") = Trim(result)
End Sub

Hope this helps / Lars-Åle

On 13 Sep, 22:59, Lars-Åke Aspelin wrote:
On Sun, 13 Sep 2009 14:38:07 -0700 (PDT), Mik

wrote:
I wish to Concatenate a Range of values.
The range will always be a single row, from column A:K, however the
row number could vary.
So, the range will be highlighted / selected by the user.

How, using VBA, do i copy the selected range and Concatenate to a
single cell?
I would also like a "space" between each concatenated value.

Try this macro:

Sub test()
* *result = ""
* *For Each c In Selection
* * *result = result & " " & c.Value
* *Next c
* *Cells(1, "L") = Trim(result)
End Sub

Hope this helps / Lars-Åle



Many Thanks.
Both Worked.

Here is a non-looping method of doing this as well...

Sub ConcatenateAtoK()
On Error Resume Next
With WorksheetFunction
Range("L1").Value = .Trim(Join(.Transpose(.Transpose(Selection))))
End With
End Sub

--
Rick (MVP - Excel)


"Mik" wrote in message
...
I wish to Concatenate a Range of values.
The range will always be a single row, from column A:K, however the
row number could vary.
So, the range will be highlighted / selected by the user.

How, using VBA, do i copy the selected range and Concatenate to a
single cell?
I would also like a "space" between each concatenated value.