What is in these cells?

**Marcol** · 03-19-2011, 02:27 PM

Often when trying to get formula to work, or indeed VBa code, there are unexpected results returned, apparently empty cells are not empty at all, but what is in them?

The cells are not so obviously "used cells"

The attached workbook illustrates my dilemma.

I put a formula in Column A, dragged it down, then Copied it and used Paste Special > Values to Column D

If in the VBa Immediate Pane you try

Please Login or Register  to view this content.

the returned value is 22, but D22 is apparently empty, this phenomenon is often highlighted by using

Please Login or Register  to view this content.

I have tried all the formula I can think of to find what is in the cell and the results are in the attached.

Select any of the yellow cells and press Delete and see the changes.

Can someone explain what is going on?

Cheers
Alistair

**martindwilson** · 03-19-2011, 02:45 PM

i should think d22 contains "" as thats the value of a22 as shown in col N

**Palmetto** · 03-19-2011, 02:49 PM

They may , in fact be truly empty.

One possibility: Excel has a "memory" issue about the last used in the range.

See this article for an explanation and how to reset the last used cell

**Marcol** · 03-19-2011, 03:01 PM

Agreed Martin, but what is the difference between the "" in D22 & D23? the formula in Column N concurs, but the VBa disagrees.

Palmetto I'll give that a better look later, but at a glance Excess Format Cleaner looks interesting. However I don't see it solving the potential pit-falls in day to day coding.

P.S.
I guess I'm maybe asking is what is an empty string?

**martindwilson** · 03-19-2011, 03:09 PM

sorry i don't follow there is nothing in a23 so when copied over there is nothing in d23 but a22 contains ""
which when copied puts "" in d22

**Colin Legg** · 03-19-2011, 03:45 PM

Hi Marcol,

Originally Posted by Marcol

but what is the difference between the "" in D22 & D23? the formula in Column N concurs, but the VBa disagrees.

I assume that by "the VBA disagrees", you are referring to the IsNull() UDF wrapper you wrote.

IsNull() checks a Variant data type for a Null value. D22 contains a zero-length string (also called a Null String), which is not the same as a Null value.
A Null value means that the Variant contains absolutely nothing (no memory allocation) whereas a Null String is a String of zero length, ie. ""

Slightly off-topic, but I thought you might be interested... you can write your UDF's a touch more concisely, for example:

Please Login or Register  to view this content.

Could be written without the If... Then statement like this:

Please Login or Register  to view this content.

**shg** · 03-19-2011, 04:14 PM

Cells on a worksheet never contain Null; that's a value reserved to VBA variables (specifically, Variants).

A null string (which is not a Null, no string can be a Null) is a zero-length string; it has no characters, but still has the data structure of a string.

EDIT: Try this

Please Login or Register  to view this content.

**MarvinP** · 03-19-2011, 04:43 PM

Hi,
I ran PUP on it and D22 shows as Text. That means there is something in it.
http://spreadsheetpage.com/index.php/pupv7/home
After selecting D22 and pressing delete the bottom of D was 21.

**shg** · 03-19-2011, 04:45 PM

VarType(someCell.Value2) will tell you the type of the variant in the cell. Using the Value2 property avoids the type conversion to Currency or Date that Value may return.

**Marcol** · 03-20-2011, 02:50 PM

This is a worm in my head

@ Colin Legg
I'm not explaining myself very well, the UDFs were just another stab at trying to identify Null Strings
Thanks for the tip.
@ shg
Thanks for that it helps to see what is going on.

The reason for pasting the values was only to replicate something that might happen in the life of a worksheet (with the original data deleted or pasted over) and then the new column would contain Null Strings and go unnoticed until something like this was tried

=COUNTA(D:D)

This returns 21 in the example given, but there are apparently only 4 values in the column
The answer is correct because there are also 17 Null Strings.

This is fairly obvious on a small sample, but if we were talking about a few thousand rows with only a few Null Strings then this would not be so easy to detect.

The Null Strings can be detected with, in a helper column, say E

Please Login or Register  to view this content.

Drag/Fill Down
Then

Please Login or Register  to view this content.

will return the expected result of 4, but this seems a bit clumsy.

Either of these

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

will return the expected result, but I don't think that would be an intuitive solution, especially so if the Null Strings were not anticipated.

The yellow cells in the attached are conditionally formatted to highlight Null Strings
Add a value to either of these columns (alpha or numeric) to see what is bugging me.

Can any one see a cleaner solution to solve this problem, or am I just paranoid about this?

**teylyn** · 03-21-2011, 01:19 AM

Maybe

=SUMPRODUCT(--(LEN(D:D)>0))

**Marcol** · 03-21-2011, 06:29 AM

Thanks teylyn that completes the picture, .....almost.

This thread has developed, in an unintended direction, that is no ones' fault but my own.
I'm still not entirely clear myself what I'm after.

However it has been quite enlightening, to me at least.

Summarizing so far.
It seems to me that Null Strings can be a potential pit-fall when counting data.

COUNT is no problem but COUNTA can be.
COUNTA returns a count of all cells that are not empty, this includes Formula that return "", and Null Strings.

Using the attached as an example

COUNT(D:D) = 4
COUNTA(D:D) = 21

Count Null Strings or Formula returning ""
COUNTIF(D:D,"<""") = 17

Count Text only excluding Null Strings and Formula returning ""
COUNTIF(K:K,">""") = 4

Count All excluding Null Strings and Formula returning ""
SUMPRODUCT(--(LEN(D:D)>0)) = 4

To highlight Null Strings and Formula returning ""
Conditional Format
=AND(ISTEXT(A1),A1="")

Now the crunch as I'm seeing it, add any formula to Column D that returns ""
This UDF

Please Login or Register  to view this content.

returns FALSE so hopefully it is identifying truely Null Strings.

My question probably should have been.
Is there a native formula that can identify Null Strings?
Or
How can I avoid entering Null Strings when copying and pasting values?

**Colin Legg** · 03-21-2011, 12:00 PM

A fringe case perhaps, but your function would return TRUE if the cell contains only a prefix character. I think a generic UDF (without error handling) would be:

Please Login or Register  to view this content.

I haven't properly tested it, so I might have missed something.

I've never needed to use a function like this, so I think any problems with this would be fairly unusual.

**Marcol** · 03-21-2011, 01:14 PM

@ Colin
I tried checking the Transition Navigation Keys Option earlier thinking that might be a way for a native formula to identify a Null String,
It helps visually in as much as when you click on a cell that has a Null String by displaying ', ^, or " depending on the cell justification, but I couldn't find a way of finding these cells with a formula.

Your suggested UDF does indeed find such cells if the prefix is manually entered, but I would want to treat such cells as a true Null String
I'll make the UDF a bit more robust as time goes by, and that is another option to test, thanks for pointing to the VBa.

My ultimate aim is to find a way to clean a sheet of all true Null Strings to aviod any errors that they might through up.

Cheers

**shg** · 03-21-2011, 01:33 PM

Or ...

Please Login or Register  to view this content.

**Marcol** · 03-21-2011, 02:03 PM

Cheers shg I was about to go the

Please Login or Register  to view this content.

route, but your way is better.

Is there a native formula that can detect a formula? I can't find one.

**shg** · 03-21-2011, 02:32 PM

No, you need the VBA HasFormula property.

**ChemistB** · 03-21-2011, 02:59 PM

You could use the old Macro 4 commands in a defined name. I use

Name = GET.CELL(48,INDIRECT("rc",FALSE))

and then conditional formatting to color in formula cells. I think shg showed me once how to direct it 1 cell to the left and then you could put = Name into the next column and get True Falses but I've forgotten. A mind is a terrible thing to waste.

**Colin Legg** · 03-21-2011, 07:03 PM

Hi Marcol,

Originally Posted by Marcol

@ Colin
Your suggested UDF does indeed find such cells if the prefix is manually entered, but I would want to treat such cells as a true Null String

In my own line of thinking, the function should return False if the cell contains only a prefix character... but, if that's what you want then the function I wrote could be simplified to:

Please Login or Register  to view this content.

What is in these cells?

LinkBack

Thread Tools

Rate This Thread

Display

What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Re: What is in these cells?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions