Add to a string to make it continuous

[Guest]

I currently have a spreadsheet that has an infinate number of rows determined
by the user.

The code states:

For row=7 to Worksheets('Example').UsedRange.Row.Count

String1 = Worksheets("Example").Cells.(Row,1).Value

Endfor

Now I am trying to accomplish it to just add to the string opposed to just
creating a new line each time. This information is finally outputted to Word.

For example: What I want
Column

Apples
Oranges
Peaches

to look like: Apples, Oranges, Peaches

What I get:

Apples, Oranges, Peaches
Apples, Oranges
Apples

is the current output. Any suggestions? Thanks

 

[Chip Pearson]

Adam,

Change
String1 = Worksheets("Example").Cells.(Row,1).Value
to
String1 = String1 & Worksheets("Example").Cells.(Row,1).Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Don Guillett]

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

 

[Guest]

Sorry, but this function does not work. It still cascades it down showing

Apples, Oranges, Peaches
Apples, Oranges
Apples

 

[Guest]

This did not seem to work either.

try
Sub stringem()
For Each C In Range("e2:e" & Cells(Rows.Count, "e").End(xlUp).Row)
mystring = mystring & C & ","
Next
MsgBox Left(mystring, Len(mystring) - 1)

End Sub

 

[Don Guillett]

Did you try my suggestion?

 

[Don Guillett]

Fully tested. How did you modify to suit your needs.

 

[Tom Ogilvy]

Don't write to word inside the loop. Build the entire string, then write to
word one time.

 

[Guest]

I got your code to work. Thanks!!

Fully tested. How did you modify to suit your needs.

 

[Dana DeLouis]

The latest versions of Excel might be able to do it this way. Just an
option:

Dim s As String
s = Join(WorksheetFunction.Transpose(Range(Cells(7, 1),
Cells(Rows.Count, 1).End(xlUp))), ",")
Debug.Print s

 

[Don Guillett]

Nice. Worked in xl2002 but join is not in xl97

 

[Dave Peterson]

Added in xl2k.

Nice. Worked in xl2002 but join is not in xl97