concantenate cells in multiple row

Pecoflyer · #1 03-25-2015, 12:54 AM

Quote:

Originally Posted by shilabrow

So are you saying its impossible to concatenate using VBA even if it outputs to Word document? All help appreciated on this. Thanks

Read my post and your question. There was never any indication of using Word.

Snakehips · #2 03-25-2015, 10:22 AM

If you are using Excel 2007 + then you can have 32,767 characters per cell although Excel cannot display that many characters.

Assuming your data starts A1 and column B is available then the following will concatenate column A data, separated by a semi-colon, in cells B1:B??. Each cell will hold approx 32000 characters.

Right click your sheet tab >> View Code and paste the below into the code pane.

Code:

Sub con()
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("B1")

For r = 1 To lr
Rng.Value = Rng.Value & ";" & Range("A" & r).Value
If Len(Rng.Value) > 32000 Then Set Rng = Rng.Offset(1, 0)
Next r
End Sub

I processed 120000 rows in under 1 minute.

When done, select the range B1:B?? Copy and paste into a word document.

Hope that helps.

shilabrow · #3 03-26-2015, 06:42 PM

Thanks Snakehips, works. Appreciate all the help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Get multiple cells in separate worksheet	kerkstrt	Excel	1	10-26-2014 11:20 PM
Link multiple cells in drop-down lists	Trial4life	Excel	0	09-08-2014 05:08 AM
Counting cells with multiple complex criteria	TishyMouse	Excel	12	12-06-2012 05:05 AM
How to merge multiple records into one and update the cells	mag	Excel	1	10-30-2012 01:11 AM
Multiple VLOOKUP's checking multiple Cells	OTPM	Excel	11	05-23-2011 11:18 AM