You want all those cells' data in one cell?

Use this UDF which ignores blank cells.

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 & ""
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A7:A100) Format cell to wrap text and row to autofit.

Note: that many cells may OD the row height limit which is 409


Gord Dibben MS Excel MVP

On Wed, 18 Jul 2007 12:54:01 -0700, robnet
wrote:

I have multi-lined text data in source cells. There is a Y/N chart for that
data with an if-then "=IF('3. VPN Configuration'!C10="Y",'4. Environment IP
Addresses'!$B6," ")" which basically states if the cell = Y, then put cell
data on another sheet, which it does just fine. (thanks to you guys).

There are numerous blank rows in each column, so I am doing a simple
concatenate twice (It does not work with more that 60 entries), then
concatenate again to get the final list. Here is the formula:
=A7&A8&A9&A10&A11&A12&A13&A14&A15&A16&A17&A18&A19 &A20&A21&A22&A23&A24&A25&A26&A27&....
then the last part: =A77&A78 (concatonates the two concatonated rows)

This is the result: (yuck!)

10.98.144.0/22
10.98.184.0/22
10.98.192.0/22 10.98.80.168

10.98.080.0/22 10.98.240.0/24 10.98.230.0/24
10.98.040.0/22 10.98.3.41(iDMZ)
10.98.3.40(iDMZ)
10.98.230.118
10.98.230.200
10.98.230.209
10.98.230.112 10.98.080.0/22 10.98.080.0/22
10.98.40.0/22
10.98.020.0/24 10.98.230.0/24 10.98.144.0/22 10.98.144.0/22
10.98.240.0/24

In the source cells, each line item is entered with "Alt+Enter" and there
are no spaces anywhere in the source.

I cannot determine how to get a final presentation of the concatenated data
to look like this: (not specifically representative of the above data)
10.98.3.41(iDMZ)
10.98.3.40(iDMZ)
10.98.230.118
10.98.230.200
10.98.230.209
10.98.230.112
etc...


Your ideas are GREATLY appreciated!!
Thanks!!