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.

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

--
Anant


"jgmiddel" wrote:

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.

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

--
Anant


"Anant Basant" wrote:

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

--
Anant


"jgmiddel" wrote:

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.

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

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

On Sat, 29 Sep 2007 12:35:01 -0700, jgmiddel wrote:

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

Thanks, this works fine, exactly what I wanted!

Thnaks a lot Gord for correcting the code.
--
Anant


"Gord Dibben" wrote:

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

On Sat, 29 Sep 2007 12:35:01 -0700, jgmiddel wrote:

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