Only adding nonzeroes
 

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.

Only adding nonzeroes
 

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.

Only adding nonzeroes
 

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.