I have a list of 100 items, which is identifyed by a formula in
Range("A" & Row).

If I decide to delete all rows where the formula in Range("A") gives an
empty string, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Rows(Counter).Delete
Next

Everything works fine... ...but...

....if I instead decide to clear the formulas, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Range("A" &
Counter).ClearContents
Next

Each and every cell gets cleared, even if the formula produces a value!!!

WHY?!?
What's the difference?!?

Why is the formulas result recognized as a value in one instance, and
not in the other???

Can someone explain?


CE

On Sun, 01 Jul 2012 18:52:11 +0200, "Charlotte E." wrote:

I have a list of 100 items, which is identifyed by a formula in
Range("A" & Row).

If I decide to delete all rows where the formula in Range("A") gives an
empty string, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Rows(Counter).Delete
Next

Everything works fine... ...but...

...if I instead decide to clear the formulas, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Range("A" &
Counter).ClearContents
Next

Each and every cell gets cleared, even if the formula produces a value!!!

WHY?!?
What's the difference?!?

Why is the formulas result recognized as a value in one instance, and
not in the other???

Can someone explain?



download a copy of a file demonstrating the problem to one of the public file-sharing sites (e.g. Skydrive). There is most likely something causing this to occur in details you have not shared here.

Charlotte E. wrote:

I have a list of 100 items, which is identifyed by a formula in
Range("A" & Row).

If I decide to delete all rows where the formula in Range("A") gives an
empty string, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Rows(Counter).Delete
Next

Everything works fine... ...but...

...if I instead decide to clear the formulas, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Range("A" &
Counter).ClearContents
Next

Each and every cell gets cleared, even if the formula produces a value!!!

WHY?!?
What's the difference?!?

Why is the formulas result recognized as a value in one instance, and
not in the other???

Can someone explain?

Works as expected for me, in a quick 10-second test. What other code do you
have running?

--
- Things aren't always so black and white.
- You're right. The important things are just green.

You are right, guys :-)

Simply a matter of using either 'Ascending' or 'Descending' when sorting
the list :-)

Stupid me!

Thanks for your time anyway....

CE


Den 01.07.2012 18:52, Charlotte E. skrev:
I have a list of 100 items, which is identifyed by a formula in
Range("A" & Row).

If I decide to delete all rows where the formula in Range("A") gives an
empty string, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Rows(Counter).Delete
Next

Everything works fine... ...but...

....if I instead decide to clear the formulas, like this:

For Counter = 100 To 2 Step -1
If Range("A" & Counter).Value = "" Then Range("A" & Counter).ClearContents
Next

Each and every cell gets cleared, even if the formula produces a value!!!

WHY?!?
What's the difference?!?

Why is the formulas result recognized as a value in one instance, and
not in the other???

Can someone explain?


CE