concantenate cells in multiple row

Snakehips · #7 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.

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