Hello all,

In Excel 2007, have a data array that contains blank lines. Is it possible
to list only the cells with values? Ill give an example:

A1='blank'
A2='blank'
A3=5/5/08
A4='blank'
A5=7/7/08

Can I list this so it ends with: 5/5/08, 7/7/08? I tried CONCATENATE, but
the blanks return ", ". And now I'm lost.

Please, post your CONCATENATE formula!
Regards,
Stefi


€žBryGuy77€� ezt Ã*rta:

Hello all,

In Excel 2007, have a data array that contains blank lines. Is it possible
to list only the cells with values? Ill give an example:

A1='blank'
A2='blank'
A3=5/5/08
A4='blank'
A5=7/7/08

Can I list this so it ends with: 5/5/08, 7/7/08? I tried CONCATENATE, but
the blanks return ", ". And now I'm lost.

To leave out the blank cells you will have to concatenate just the ones with
data.

=A3 & "," & A5

Or use this UDF which ignores blank cells and comma-delimits those with data.

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

Usage is =concatrange(A1:A5) which returns 5/5/08,7/7/08

If you're not familiar with VBA and macros/functions, see David McRitchie's site
for more on "getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or Ron de De Bruin's site on where to store macros.

http://www.rondebruin.nl/code.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

In a cell enter the formula as shown above in Usage is:


Gord Dibben MS Excel MVP


Gord Dibben MS Excel MVP

On Tue, 20 May 2008 21:54:01 -0700, BryGuy77
wrote:

Hello all,

In Excel 2007, have a data array that contains blank lines. Is it possible
to list only the cells with values? Ill give an example:

A1='blank'
A2='blank'
A3=5/5/08
A4='blank'
A5=7/7/08

Can I list this so it ends with: 5/5/08, 7/7/08? I tried CONCATENATE, but
the blanks return ", ". And now I'm lost.