detect empty cell

M

mike allen

I have haggled w/ this forever it seems. I want a universal (old and new
excel versions) way to see if a cell has anything in it. It may be text,
number, or anything.
in excel2000, ...if cells(i,j) <> 0 then... worked, but it didn't work
in an earlier version (I think 97).
....if application.isempty(cells(i,j)) then... doesn't work, but ...if
isempty(cells(i,j)) then... works in 2000. This is interesting b/c I have
come across instances where putting 'application' in front of a command
allows it to work in 97, while it worked either way in 2000.

If I don't see anything in the cell, I expect it to detect this empty field
and skip over it, regardless of what version of excel the user has and
regardless of whether I am looking for text or numbers or anything else. Is
there a sure-fire way to accomplish this? thanks, mike allen
 
K

keepITcool

run this and never do it again.. ..although it IS fractional:)

Sub ff()
Dim t!, i&, b As Boolean
t = Timer
For i = 1 To 100000
b = IsEmpty(Cells(1, 1))
Next
Debug.Print Timer - t, "e"
t = Timer
For i = 1 To 100000
b = Cells(1, 1) = ""
Next
Debug.Print Timer - t, "-"
t = Timer
For i = 1 To 100000
b = Len(Cells(1, 1)) = 0
Next
Debug.Print Timer - t, "L"

End Sub

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
T

Tom Ogilvy

That assumes the cells are really empty. The time difference is hardly
significant enough to warrant "never do it again" and len(trim(cells(1,1))
is more robust in situations where people clear cells with a spacebar.

Just another opinion.
 
K

keepITcool

Tom Ogilvy said:
That assumes the cells are really empty.
Click to expand...

THIS was the question:
"way to see if a cell has anything in it. It may be text,
number, or anything."


The time difference is
hardly significant enough to warrant "never do it again"
Click to expand...

I said "..although it IS fractional:)"

the smiley was there for a reason...

len(trim(cells(1,1)) is more robust in situations where people clear
cells with a spacebar.

Just another opinion.
Click to expand...

When testing for empty or 'spaced' cells, your solution
is (as always) impeccable

<vbg>





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
T

Tom Ogilvy

Not to be argumentative, but
the real question was

"If I don't see anything in the cell, I expect it to detect this empty field
and skip over it, regardless of what version"

your quote referred to peripheral information -- the fact that he could be
looking at cells that contain anything. So if it looks empty, regardless
of what it contains, treat it as empty.
 
J

Joseph

I did something similar to this recently, and I just used Do Until:

Do Until len(Activecell.Value)<1
'Your Code
Loop

It should probably work across all versions.

cheer
 
Top