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