Values from a range in one cell, seperated

[jgmiddel]

In a range "stores" I have some values. What I want is to put all this
data in one cell, seperated by a ";" and a space. If there is only one
row filled in, that ";" should not be placed. Can anyone help? Thanks
in advance.

 

[Guest]

Hi,

I have these four values in a worksheet range A1:A4

Name1
Name2
Name3
Name4

I select the range of cells and run the following macro:

Sub ConcatData()
Dim myStr As String
Dim c As Range

For Each c In Selection.Cells
If Not IsEmpty(c) Then
myStr = myStr & "; " & c.Value
End If
Next c
myStr = Right(myStr, Len(myStr) - 2)
End Sub

 

[Guest]

A small correction:

Sub ConcatData()
Dim myStr As String
Dim c As Range

For Each c In Selection.Cells
If Not IsEmpty(c) Then
myStr = myStr & "; " & c.Value
End If
Next c
If Not myStr = "" Then
myStr = Right(myStr, Len(myStr) - 2)
End If
End Sub

 

[jgmiddel]

Thanks for responding, but it doesn't work. I tested it in Excel 2007
and 2003

 

[Gord Dibben]

Needs a cell to enter the results. Also you mentioned having a "stores" range.

Sub ConcatData()
Dim myStr As String
Dim c As Range
For Each c In Range("stores")
If Not IsEmpty(c) Then
myStr = myStr & "; " & c.Value
End If
Next c
If Not myStr = "" Then
myStr = Right(myStr, Len(myStr) - 2)
Range("J3").Value = myStr
End If
End Sub


Gord Dibben MS Excel MVP

 

[jgmiddel]

Thanks, this works fine, exactly what I wanted!

 

[Guest]

Thnaks a lot Gord for correcting the code.