PDA

View Full Version : [SOLVED] Concatenate Rows Of Data



bilbo85
05-09-2017, 04:06 PM
Hi,

I have thousands of rows of data in one column that I am trying to concatenate but it is proving not possible with the concatenate function as there are too many rows. Do you have a macro that could concatenate the rows (each separated by a comma) and then return the value rather than the formula please?

I know how to do this in Word by replacing ^p with a comma but I need a macro in Excel if possible.

Thanks very much.

SamT
05-09-2017, 06:35 PM
You want to concatenate thousands of Strings into one String? The EntireColumn.

There is a limit to String size, and you might reach that limit. What then?

bilbo85
05-09-2017, 06:57 PM
Yes, that's correct. Do you know what the limit is? Actually, I could probably limit the number of rows to around 500 if that helps.

mdmackillop
05-10-2017, 06:00 AM
Function DoConCat(Data As Range) As String
Dim txt As String
Dim i
txt = Data(1)
For i = 2 To Data.Rows.Count
txt = txt & "," & Data(i)
Next i
DoConCat = txt
End Function

bilbo85
05-10-2017, 10:37 AM
Excellent, thank you!