I have been having problems with a large workbook which I have tracked down to an odd behaviour where a cell seems to be both blank and not blank !

There is a named range, let's call it GreenBox, and to illustrate the issue I have used two ways of calculating how many cells it contains:

The first method multiplies the number of rows by the number of columns - and gives the correct result:
=ROWS(GreenBox)*COLUMNS(GreenBox)

The second method sums the number of filled cells and the number of blank cells and gives a number of cells that is 1 greater then the first method:
=COUNTA(GreenBox)+COUNTBLANK(GreenBox)

I finally pinpointed the issue when I found one cell which returns a value of 1 from COUNTA and a value of 1 from COUNTBLANK.

I can't see how this is possible ?

If I copy the offending cell into another part of the GreenBox range, the discrepancy rises to 2 - as I would expect. No contents are visible in the offending cell and deleting its contents makes no difference. But erasing the cell completely with Clear All sorts out the discrepancy.

The discrepancy arises from time to time - I have no idea how - and I would like to undertand what is going on so that I can stop it from happening.

I have a small workbook which illustrates the issue but I can't attach it here because it has a lot of personal customisation in the ribbon which gives away personal information. I would post it if I could find a way of making this one workbook have no customisation.

Appreciate any help / ideas.

I have discovered that if I select the offending cell, click in the formula bar and then click on return the discrepancy disappears.

That doesn't make sense to me either.

I have also checked the LEN function which returns 0.

Your cell probably contains an empty text string (""), which will return 1 with both functions. Depending on what you have in the other cells there are ways to ignore that. Please post a sample sheet with some data and manually mocked up results if needed
To check if A1, say, has an empty text string, type =TYPE(A1) somewhere. If the result is 2, it is text.
Now, how did it get there ?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

The following will remove all 'empty' cells in your workbook. Test on a COPY OF YOUR WORKBOOK for safety reasons. Test for file size before then after running macro.

Type(XXX) does return 2. Very helpful, thank you.
There are lots of other TEXT cells on the worksheet but they all contain text, so they return 0 for the COUNTBLANK function.
That gives me enough clues to write a procedure to identify any cells with the empty text string. I'll do that in VBA as it is a very large worksheet.
I too wonder how these empty strings crop up from time to time - not often though.
Thanks again
Martin

Thanks, but that isn't addressing my issue. Your procedure clears cells outside the "used" area. My "problem" cell(s) is within the used area.

See my reply to PECOFLYER

Cheers

Martin

A small Power Query solution will clean that up.
Text is highlighted in yellow. After transformation, the null string has disappeared

Attached Files

No Null Strings.xlsx (17.8 KB, 1 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thanks again
I couldn't work out how to detect the errant cell in "pure" VBA but a simple use of the Worksheet functions did the trick - here's the critical line

Now to find out how this happens

Martin