Empty Cells, COUNTBLANK, and ISBLANK

[Jim Watkins]

Both COUNTBLANK and ISBLANK relate to empty cells. Yet I
have a spread sheet that provides the following seemingly
contradictory results:
* The calculated result of "=ISBLANK(I7)" is FALSE
* The result of "=COUNTBLANK(I7)" is 1 ("one").

The spreadsheet was downloaded so I don't know precisely
what IS in the cell, other than the fact that nothing is
displayed in cell I7. Call you help me?

I am using Windows XP and EXCEL 2002.

 

[Paul B]

Jim, do you see anything in the formula bar when you select I7? If I7 has a
" ' " in it you want see it in the cell but you will get a false and a 1
with the formulas

--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

 

[Ron Rosenfeld]

Both COUNTBLANK and ISBLANK relate to empty cells. Yet I
have a spread sheet that provides the following seemingly
contradictory results:
* The calculated result of "=ISBLANK(I7)" is FALSE
* The result of "=COUNTBLANK(I7)" is 1 ("one").

The spreadsheet was downloaded so I don't know precisely
what IS in the cell, other than the fact that nothing is
displayed in cell I7. Call you help me?

I am using Windows XP and EXCEL 2002.

There may be a formula in I7 that is returning "".
Try selecting I7 and see what is in the formula bar. Note that if the
worksheet is protected, you may not be able to see the contents of I7 so you'll
have to unprotect it.


--ron

 

[Jim Watkins]

The formula bar displays nothing....

-----Original Message-----

 

[Alan Beban]

The cell no doubt has in it what I call a "string blank"; i.e., the
empty cell (=""), ',etc. An additional clue would be that
=COUNTIF(I7,"=") returns 0 while =COUNTIF(I7,"") returns 1; the first is
returning the number of empty cells (what I call "empty blanks"), the
second the number of empty blanks and string blanks combined.

Alan Beban

 

[Ron Rosenfeld]

The formula bar displays nothing....

Is the worksheet protected?


--ron

 

[Dave Peterson]

If the original workbook had formulas that evaluated to "", then were converted
to values, the cells might look blank, but they aren't.

Turn on Tools|Options|Transition Tab|Transition Navigation Keys

Select that cell and look in the formula bar. It isn't empty. You'll see a
single quote left behind.

If this is what happened, you can clean them up:

Now try selecting a few of those cells and do
Edit|Replace
Find what: (leave blank)
Replace with: $$$$$ (some unique set of characters)
Replace all

Tnen do the opposite
Edit|Replace
find what: $$$$$ (same unique characters)
replace with: (leave blank)
Replace all

You could add code that does the same kind of thing.

Another way to get rid of those single quotes is:

With Worksheets("sheet1").Range("a1:a9999")
.Value = .Value
End With

The only difference I've seen in these two approaches is when you have a cell
that has a mixed format (some characters bold, some not. Some red, some not).
The .value loses that character by character formatting.

Don't forget to turn off that transition navigation keys stuff.

 

[Jim Watkins]

Tools > Protection only gives me a "Protect Sheet"
option. I presume that this means that the sheet is
currently unprotected. Is there anyway that I could
attach the spreadsheet itself to an email, so that you or
someone could inspect and experiment directly? I've got
a stripped down version available with only 3 filled
cells: the mysterious cell itself, and two cells with the
results of the IFBLANK and COUNTIF functions....

-----Original Message-----

 

[Alan Beban]

Did you not get Dave Peterson's post in this thread at 8:36 last night?
This looks like a good bet to get at your problem.

Alan Beban

Tools > Protection only gives me a "Protect Sheet"
option. I presume that this means that the sheet is
currently unprotected. Is there anyway that I could
attach the spreadsheet itself to an email, so that you or
someone could inspect and experiment directly? I've got
a stripped down version available with only 3 filled
cells: the mysterious cell itself, and two cells with the
results of the IFBLANK and COUNTIF functions....

 

[Ron Rosenfeld]

Tools > Protection only gives me a "Protect Sheet"
option. I presume that this means that the sheet is
currently unprotected. Is there anyway that I could
attach the spreadsheet itself to an email, so that you or
someone could inspect and experiment directly? I've got
a stripped down version available with only 3 filled
cells: the mysterious cell itself, and two cells with the
results of the IFBLANK and COUNTIF functions....

-----Original Message-----

Sure, mail it to me at

--reverse this address to make it work --

ten.aidaca@dlefnesornor



--ron

 

[Ron Rosenfeld]

Another way to get rid of those single quotes is:

With Worksheets("sheet1").Range("a1:a9999")
.Value = .Value
End With

Alan,

With this macro, a formula in a cell that evaluates to "" will also be CLEAR'd.


--ron

 

[Alan Beban]

Alan,

With this macro, a formula in a cell that evaluates to "" will also be CLEAR'd.


--ron

Hi Ron,

Your comment should be directed to Dave Peterson (though I expect he
will see it). I simply reposted Dave's post (in which the above
suggestion was included) at the specific request of the OP at 1:26pm on
9/17.

Alan Beban

 

[Ron Rosenfeld]

Hi Ron,

Your comment should be directed to Dave Peterson (though I expect he
will see it). I simply reposted Dave's post (in which the above
suggestion was included) at the specific request of the OP at 1:26pm on
9/17.

Alan Beban

Oh, yeah. I see now where you mentioned that you had reposted Dave's comments.
But I did not see Dave's comments, either!

In any event, the OP email'd me the workbook. Indeed there is a null string
("") in the errant cell, with no formula. And that can be reproduced in a
variety of ways.


--ron

 

[Ron Rosenfeld]

This is what's evil about responding to private e-mail requests - you don't tell
the group as much as you may have done the OP in private e-mail.

But sometimes I like being evil <g>.

In any event, the non code suggestion previously posted worked fine.

And I did use a slightly different code snippet:

If rg.Value = "" And Not rg.HasFormula Then
rg.Clear
End If


--ron

 

[Dave Peterson]

You're right about the cells that contain formulas that evaluate to "".

I figured that if the OP has those cells with just a single quote hanging
around, they were probably caused by copy|paste special values. And I jumped to
the conclusion that the range contained no formulas. I should have added that
caveat.

I'm not sure if this would be any quicker than looping through cells, but:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myArea As Range

With Worksheets("sheet1")
On Error Resume Next
Set myRng = .Range("a1:a9999").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each myArea In myRng.Areas
myArea.Value = myArea.Value
Next myArea
End If
End With

End Sub