Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Sort of emtpy cells

Posted on 2012-03-19
21
183 Views
Last Modified: 2012-03-19
I have a 2003 Excel Sheet in that before it prints its does some checking of data for missing data (user form)

I have a cell that when I edit it (F2), view it, or msgbox it, its emtpy... however in my code "IsEmpty" is thinks its not emtpy.  If I edit the cell and just press delete then it treats it as emtpy as nothing was in the cell and the cursor doesn't move left when I "deleted" the text).

What is causing this and how can I prevent or check for these mysterious characters?  I would like to remove them so I can truly check if the cell is empty.

Thank you.
0
Comment
Question by:thandel
  • 7
  • 7
  • 5
  • +2
21 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37739633
Most likely a space.  EG:  " " 

Try the trim function in your code, if there is no trim:  Replace(" ", "")  (eg: replace blank spaces with nothing).  Note: this is for the initial check only of IsEmpty.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37739641
It probably contains a Tab or some other unprintable character.
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37739679
It might be an empty string ("").  To test you can try these in the immediate window.

?isempty(Range("A1"))
?Range("A1") = vbnullstring

For a blank string IsEmpty will return false and vbnullstring will return true.

Kyle
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:thandel
ID: 37739810
I am using isempty now...  vbnullstring it seems to catch this condition.  What is the difference? Can I not trust isempty anymore?
0
 

Author Comment

by:thandel
ID: 37739816
Thanks Ged but already trimmed and there isn't any spaces.... just nothing in the cell... but there is!?!?!?!?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37739885
Do Debug.Print Asc(cell contents).

What does it show?
0
 

Author Comment

by:thandel
ID: 37739912
I just entered: Do Debug.Print Asc(I19) and got a compile error of expected while or until.
0
 

Author Comment

by:thandel
ID: 37739913
Sorry I tried Debug.Print Asc(I19) but got an error of : "Run time error 5"
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37739930
It should be

Debug.Print Asc(Range("I19").Value)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37740100
use:

Range("I19").Value = Replace(WorksheetFunction.Clean(WorksheetFunction.Trim(Range("I19").Value)), Chr(160), vbNullString)

You can try that in the immediate window if you want, then type the following:

debug.print Len(Range("I19"))

You SHOULD get a zero length string after all that...

This should trim multiple spaces, remove the nonprintables (most) and the chr(160) nonprintable.  Likely this gets the high end if not all of the nonprintable space you'd be dealing with.

PS - the IsEmpty() function by definition identifies whether a variant variable has been initialized.  Its not for testing cell ranges, but to advise if a variant variable is holding some type of data.

Select the IsEmpty word in your code and hit F1 for more help on the topic.

Dave
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37740189
Nice code dlmille.
0
 

Author Comment

by:thandel
ID: 37740328
Thank you... however this doesn't seem to be "cleaning" the cell.  If I enter a space.... it removes the space but Isempty still thinks its not empty.  After running Replace(WorksheetFunction.Clean(WorksheetFunction.Trim(Range("I19").Value)), Chr(160), vbNullString) what is left in the cell to still think there is something in the cell?

Should I be using a different type of check?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37740330
Please review my post.

The IsEmpty() function is not for checking a cells content.

did you check the Length of what was in the cell?

Debug.Print Len(Range("I19").Value)

?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37740332
Did you do?

Debug.Print Asc(Range("I19").Value)
0
 

Author Comment

by:thandel
ID: 37740335
Hmmm I added this after your cleanup and it seems to work with isempty better.

If Len(Target.Value) = 0 Then Target.Value = Null
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37740341
If Len(Target.Value) = 0

then Target.Value is null - that is a truism.  You don't need to set it.  If its length is zero, there is nothing there ,:)
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37740343
Again, IsEmpty() is NOT a function designed to do what you're trying to use it for.  

It is for testing the contents of Variant variables.  See my original post

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37740348
Of course, if you populate a variant with a cell's contents, then you can use IsEmpty, sure.

Perhaps that's what you're doing?

Like:

IsEmpty(Selection) ???

or

dim myCheck as variant

mycheck = range("A19").value

if IsEmpty(myCheck) then

is this what you're doing?

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37740349
Regardless, if a range's value = vbNullString, or if the length of a range's value is zero, then you can guarantee there's no data in that cell.

Cheers,

Dave
0
 

Author Comment

by:thandel
ID: 37740398
Thanks Dave that was what I was doing IsEmpty(Selection).... but now I am modifying the code to use Len to check. Thanks!
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37740400
I see - yes, I can see how the "raw" use of selection in the function would translate to variant.  Thanks for clarifying your use.

Dave
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question