Counting blanks
Posted by rob on January 17, 2002 7:06 AM
I need a statement that counts blank cells and returns 0 if there is no blank spaces. I've tried:-
Selection.SpecialCells(xlCellTypeBlanks).count
But if there are no blank spaces it return error 1004 run time error. So if possible I'd like a one line statement that return 0 instead of an error.
Any help appreciated. The emphasis on this is to keep the code as short as possible.
Posted by dan on January 17, 2002 7:49 AM
Don't think you can do it with one line. You're asking it to count cells that aren't there when you get the error. Even the IsError won't work in this case. Afraid you'll need to use something else like:
CountBlank = 0
For Each cell In Selection
If cell.Value = "" Then CountBlank = CountBlank + 1
Next cell
Posted by Tom Urtis on January 17, 2002 7:59 AM
Just an idea, maybe N/A in your case...
Dan's method may be just what you're after, but depending on what you are doing and how you are presenting it, you can produce the value via a message box, in this example with a hard coded range:
MsgBox WorksheetFunction.CountBlank(Range("A1:A20")), , "Total number of blank cells is..."
It's on one line, but may not be what you want, so it's just an FYI.
Tom Urtis
