Concatenate values

[Mik]

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.

 

[Per Jessen]

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

 

[Lars-Åke Aspelin]

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

 

[Mik]

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.

 

[Rick Rothstein]

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