Concatenate values

M

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.
 
P

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
 
L

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
 
M

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.
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top