URGENT:Concatenating 1000 cells into 1

[es9004]

I need to concatenate a 1000 cells into a single cell. Could someon
give me a macro to help me do that? Or if you have any ideas do let m
know.

Thank

 

[William]

Hi

Sub test()
Dim c As Range, s As String
s = ""
'Amend range as appropriate
For Each c In Range("A1:A1000")
s = s & " " & c
Next c
s = Trim(s)
Range("B1") = s
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I need to concatenate a 1000 cells into a single cell. Could someone
| give me a macro to help me do that? Or if you have any ideas do let me
| know.
|
| Thanks
|
|
| ---
| Message posted
|

 

[Harlan Grove]

I need to concatenate a 1000 cells into a single cell. Could someone
give me a macro to help me do that? Or if you have any ideas do let me
know.

Unless the text in each of these 1000 cells is fairly short, the result won't
fit into a single cell. What do you get from

=SUMPRODUCT(LEN(Your1000Cells))

?

 

[Anders Silven]

What do you get from

=SUMPRODUCT(LEN(Your1000Cells))

Excuse me for interrupting Harlan, this is not my thread, but I get the number
of characters in the range, regardless if the range contains numbers or text or
both. What do you expect?

Anders Silven
XL2002

 

[Dave Peterson]

Excel's cells can hold about 32k characters.

Harlan's formula will show what the length of the final concatenated cell would
be.

If it exceeds 32k, then the OP would be in trouble.

 

[Harlan Grove]

Excuse me for interrupting Harlan, this is not my thread, but I
get the number of characters in the range, regardless if the range
contains numbers or text or both. What do you expect?

I expect what you stated. That's why I asked. It's a diagnostic. Look up the
word 'diagnostic' in the dictionary, and you may gain enlightenment (but the
odds are against it).