how can I delete cells that have a formula and that formula equals 0 (zero)
in one column and #value! in a other column?
eg:- a b
1 0 #value!
2 234.3 3.78
3 0 #value!
4 327 4.712
5 54 23
6 0 #value!
7 0 #value!
8 156 89.7
and so on down to 300 rows . the zero and value cells are not always in the
same order they are in random order

can some one please help?

thanks bill

Hi,

Use Filter-Auto filter and select those rows (usign the condition that
value is zero in first column and error message in second column) and
then delete those rows.

Govind.

bill gras wrote:

how can I delete cells that have a formula and that formula equals 0 (zero)
in one column and #value! in a other column?
eg:- a b
1 0 #value!
2 234.3 3.78
3 0 #value!
4 327 4.712
5 54 23
6 0 #value!
7 0 #value!
8 156 89.7
and so on down to 300 rows . the zero and value cells are not always in the
same order they are in random order

can some one please help?

thanks bill

Hi Govind
Thank you for your reply
Is there an other way with a formula because the next day I have to do this
all over again
thanks

"Govind" wrote:

Hi,

Use Filter-Auto filter and select those rows (usign the condition that
value is zero in first column and error message in second column) and
then delete those rows.

Govind.

bill gras wrote:

how can I delete cells that have a formula and that formula equals 0 (zero)
in one column and #value! in a other column?
eg:- a b
1 0 #value!
2 234.3 3.78
3 0 #value!
4 327 4.712
5 54 23
6 0 #value!
7 0 #value!
8 156 89.7
and so on down to 300 rows . the zero and value cells are not always in the
same order they are in random order

can some one please help?

thanks bill

"bill gras" wrote
... Is there an other way with a formula
because the next day I have to do this all over again ..

One non-array formulas play to make it dynamic ..

Assume data is in Sheet1, cols A & B, data from row1 down

Using an empty col to the right, say col D,

Put in D1:
=IF(COUNTBLANK(A1:B1)=2,"",IF(AND(A1=0,ISERROR(B1) ),"",ROW()))

Copy D1 down to say, D500, to cover the max expected data in cols A & B
(can copy down ahead of data input)

In Sheet2
--------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))

Copy A1 across to B1, fill down to B500
(cover the same range as done in col D in Sheet1)

Sheet2 will return only the desired rows from Sheet1's cols A & B, all
neatly bunched at the top.

For the sample data posted, you'd get:

234.3 3.78
327 4.712
54 23
156 89.7
(blank rows below)

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Thanks Max
you are a genius and a gentelman!

"Max" wrote:

"bill gras" wrote
... Is there an other way with a formula
because the next day I have to do this all over again ..

One non-array formulas play to make it dynamic ..

Assume data is in Sheet1, cols A & B, data from row1 down

Using an empty col to the right, say col D,

Put in D1:
=IF(COUNTBLANK(A1:B1)=2,"",IF(AND(A1=0,ISERROR(B1) ),"",ROW()))

Copy D1 down to say, D500, to cover the max expected data in cols A & B
(can copy down ahead of data input)

In Sheet2
--------
Put in A1:

=IF(ISERROR(SMALL(Sheet1!$D:$D,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$D:$D,ROWS($A$1:A1)),Sheet1!$D:$D,0)))

Copy A1 across to B1, fill down to B500
(cover the same range as done in col D in Sheet1)

Sheet2 will return only the desired rows from Sheet1's cols A & B, all
neatly bunched at the top.

For the sample data posted, you'd get:

234.3 3.78
327 4.712
54 23
156 89.7
(blank rows below)

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"bill gras" wrote in message
...
Thanks Max
you are a genius and a gentelman!

Is there a way to get excel 2000 to input zero's into blank spaces with a
macro? useing edit & got to etc don't work
curt

"Max" wrote:

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"bill gras" wrote in message
...
Thanks Max
you are a genius and a gentelman!

You have more followups at your original thread.

Curt wrote:

Is there a way to get excel 2000 to input zero's into blank spaces with a
macro? useing edit & got to etc don't work
curt

"Max" wrote:

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"bill gras" wrote in message
...
Thanks Max
you are a genius and a gentelman!




--

Dave Peterson

You have more followups at your original thread.

Where is that? I'm actually trying my hand at some VBA and would like to see
how you pros code it!

Biff

"Dave Peterson" wrote in message
...
You have more followups at your original thread.

Curt wrote:

Is there a way to get excel 2000 to input zero's into blank spaces with a
macro? useing edit & got to etc don't work
curt

"Max" wrote:

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"bill gras" wrote in message
...
Thanks Max
you are a genius and a gentelman!




--

Dave Peterson

You have more followups at your original thread.

Where is that?

Nevermind, I found it!

Biff

"Biff" wrote in message
...
You have more followups at your original thread.

Where is that? I'm actually trying my hand at some VBA and would like to
see how you pros code it!

Biff

"Dave Peterson" wrote in message
...
You have more followups at your original thread.

Curt wrote:

Is there a way to get excel 2000 to input zero's into blank spaces with
a
macro? useing edit & got to etc don't work
curt

"Max" wrote:

You're welcome !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"bill gras" wrote in message
...
Thanks Max
you are a genius and a gentelman!




--

Dave Peterson