Concatenate Rows Of Data

[bilbo85]

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]

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]

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]

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]

Excellent, thank you!