How many cells are you talking about and how much text is in them?

What you want can be done but may not be practical due to some limits in Excel.

Maybe some other method could be employed?

If not, use this UDF to stick the text into one cell....comma delimited as
written and ignores blank cells in the range.

If don't want a comma just delete that from the code.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & ","
'remove comma
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

Usage is: ConCatRange(A1:A10)


Gord Dibben MS Excel MVP

On Wed, 28 Jun 2006 10:49:01 -0700, SharonGaila
wrote:

I have a continous column of cells with text in them. I want to consolidate
all of the information in those cells into one cell in another worksheet.
Any suggestions as to how I can accomplish this? FYI: copy and paste link
does not work.