Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

Recognizing blank cells in Excel

Posted by yogesh k. potdar on October 18, 2001 5:48 AM

How do I put a condition statement in a macro to detect blank cells

I tried:

for j=1 to 5
test = Cells(j, 6).Value

if test <> "" Then
.....
Endif
Next

I expect it to not enter If loop for all j's where
6th column is blank. But this does not happen..

Check out our Excel VBA Resources

Re: Recognizing blank cells in Excel

Posted by Juan Pablo on October 18, 2001 6:40 AM

Try

for j=1 to 5
If Not IsEmpty(Cells(j, 6)) <> "" Then
.....
Endif
Next

Juan Pablo

Re: Recognizing blank cells in Excel

Posted by yogesh k. potdar on October 18, 2001 6:53 AM

The suggestion give "Type Mismatch" error ? Any thoughts??? Try

Re: Recognizing blank cells in Excel

Posted by Aladin Akyurek on October 18, 2001 6:59 AM

Juan,

Why not use an equivalent of =LEN(A1)>0?

Aladin

Re: Recognizing blank cells in Excel

Posted by Juan Pablo on October 18, 2001 7:11 AM

I think you could use it, but anyway, i forgot to erase the <> "" part. It should read

If Not IsBlank(Cells(j, 6)) then

I guess using

If Len(Cells(j, 6))> 0 then

should work as well. I like the first one because it's "easier" to understand when you're debugging...

Juan Pablo Juan, Why not use an equivalent of =LEN(A1)>0? Aladin

Re: Recognizing blank cells in Excel

Posted by Jonathan on October 18, 2001 7:36 AM

Here's a slightly different approach

If Application.WorksheetFunction.CountBlank(ActiveCell) = 0 Then
MsgBox "Cell not blank"
Else
MsgBox "Cell is Blank"
End If

The suggestion give "Type Mismatch" error ? Any thoughts??? : Try : for j=1 to 5

Re: Recognizing blank cells in Excel

Posted by Aladin Akyurek on October 18, 2001 7:41 AM

If Not IsBlank(Cells(j, 6)) then I guess using If Len(Cells(j, 6))> 0 then should work as well. I like the first one because it's "easier" to understand when you're debugging...

I didn't suggest it in order to help you out. There is no need for that. I knew you'd use the equivalent of ISBLANK. Because of that ephemeral problem of blank vs empty, I thought you might be interested in using of the VBA equivalent of LEN...

Aladin

I know you were, it's cleaner, faster, but a little more "difficult"... thanks (NT)

Posted by Juan Pablo on October 18, 2001 7:49 AM

Re: Len worked where ISEmpty didnot :)

Posted by yogesh k. potdar on October 18, 2001 10:35 AM

My Excel file does not seem to recognize "Empty"
cells correctly, its origianally imported from a test editor..And Excel'97 - that I use - does not
recognize ISBlank..I don't know WHY???

anyway.. LEN thing worked fine.. and I was able to use it in a couple of other palces as well..

thanks,

-Yogesh

Re: Len worked where ISEmpty didnot :)

Posted by Juan Pablo on October 18, 2001 1:09 PM

Don't know what am i thinking... it's not IsBlank, it should work with IsEmpty... but if you imported them from a text editor maybe excel doesn't recognize the cell as "empty".

Juan Pablo My Excel file does not seem to recognize "Empty"

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.