Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Sort of emtpy cells

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
thandel
Asked:
thandel
  • 7
  • 7
  • 5
  • +2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
Martin LissRetired ProgrammerCommented:
It probably contains a Tab or some other unprintable character.
0
 
kgerbChief EngineerCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
thandelAuthor Commented:
I am using isempty now...  vbnullstring it seems to catch this condition.  What is the difference? Can I not trust isempty anymore?
0
 
thandelAuthor Commented:
Thanks Ged but already trimmed and there isn't any spaces.... just nothing in the cell... but there is!?!?!?!?
0
 
Martin LissRetired ProgrammerCommented:
Do Debug.Print Asc(cell contents).

What does it show?
0
 
thandelAuthor Commented:
I just entered: Do Debug.Print Asc(I19) and got a compile error of expected while or until.
0
 
thandelAuthor Commented:
Sorry I tried Debug.Print Asc(I19) but got an error of : "Run time error 5"
0
 
Martin LissRetired ProgrammerCommented:
It should be

Debug.Print Asc(Range("I19").Value)
0
 
dlmilleCommented:
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
 
Martin LissRetired ProgrammerCommented:
Nice code dlmille.
0
 
thandelAuthor Commented:
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
 
dlmilleCommented:
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
 
Martin LissRetired ProgrammerCommented:
Did you do?

Debug.Print Asc(Range("I19").Value)
0
 
thandelAuthor Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
thandelAuthor Commented:
Thanks Dave that was what I was doing IsEmpty(Selection).... but now I am modifying the code to use Len to check. Thanks!
0
 
dlmilleCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 7
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now