detect empty cell
 

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

detect empty cell
 

use:
not isempty(Range("a1"))
or
Range("a1")<""

..value is optional as it's the default property of the range


keepITcool

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


"mike allen" wrote:

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

detect empty cell
 

I've always used "if(len(cells(i,j))=0"

Stan

"mike allen" wrote in message
...
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

detect empty cell
 

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


"Stan Scott" wrote:

I've always used "if(len(cells(i,j))=0"

Stan

"mike allen" wrote in message
...
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

detect empty cell
 

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.

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
...
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


"Stan Scott" wrote:

I've always used "if(len(cells(i,j))=0"

Stan

"mike allen" wrote in message
...
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

detect empty cell
 

"Tom Ogilvy" wrote:

That assumes the cells are really empty.

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"

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.

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

detect empty cell
 

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.

--
Regards,
Tom Ogilvy

"keepITcool" wrote in message
...
"Tom Ogilvy" wrote:

That assumes the cells are really empty.

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"

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.

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

detect empty cell
 

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

--
Message posted from http://www.ExcelForum.com