Column of data to one string

[es2008]

Hi,

I am trying to get a column of data:

Column A
1
2
3
4
5

and have it send the data as value in one string separated by comma and with single quotes around each data.

Coumn A
'1','2','3','4','5'

I came up with this but it isn't working properly:


Range("b1").Select

Dim row As Integer
Dim entry, ending As String
Dim the_end As Boolean

row = 1
the_end = False

While the_end = False

ending = Range("a" & row).Value
If ending = "end" Then
the_end = True
Else
entry = entry & "' ,'" & Range("a" & row).Value
End If
row = row + 1

Wend
Range("a" & row).Value = entry


Results come out like this:
,'1','2','3','4','5

It starts with a comma and doesn't end properly with a single quote.
Help.

[Carl A]

Here is a hack of sorts it'll work maybe someone with a little more knowledge can show you a more elegant way.

This assumes that your left character position will always be the same
Wend
Range("a" & row).Value = entry
Range("a" & row).Characters(2, 1).Delete
entry = Range("a" & row).Value
Range("a" & row).Value = entry & "'"

[Slyboots]

Do it the other way round:

entry = entry & "'" & range("a" & row).value & "',"

This will result in:

'1','2','3','4','5',

so add this line to lop off the ending comma:

entry = left(entry,len(entry)-1)

S

[Bob Phillips]

Range("b1").Select

Dim row As Integer
Dim entry, ending As String
Dim the_end As Boolean

row = 1
the_end = False
entry = "''"

While the_end = False

ending = Range("a" & row).Value
If ending = "end" Then
the_end = True
Else
entry = entry & Range("a" & row).Value & "' ,'"
End If
row = row + 1

Wend
Range("a" & row).Value = Left$(entry, Len(entry) - 2)

[es2008]

Thank you that worked wonderfully. However, it does adds the extra "," right in the front when I have more than 50 rows. STrange... would you know the reason for this?

Thanks

[mikerickson]

You might want to use a UDF.
Function ConcatinateRange(inputRange As Range, Delimiter As String) As String
ConcatinateRange = Join(inputRange.Value, Delimiter)
End Function

=CHAR(39)&ConcatinateRange(B1:B100,CHAR(39)&","&CHAR(39))&CHAR(39)

(I used CHAR(39) because I find "'" hard to read)

OFFSET(B1,0,0,MATCH("end",B:B,0)-1,1)) could be used instead of the explicit range.

[Bob Phillips]

Thank you that worked wonderfully. However, it does adds the extra "," right in the front when I have more than 50 rows. STrange... would you know the reason for this?

Thanks

That's very strange. I have just tried it with 55 rows and I didn't get the extra ,.