Checking for empty cells in a range

[Chris Strug]

Hi,

Probably a silly question but I'm not sure of the best way to achieve it.

Basically, given a range (say A7:A30) what's the best way to see if any of
these cells are empty / null / blank. I don't necessarily need to know which
ones simply that there is one or more blank cell present.

Any advice, links or any other help is gratefully received.

Thanks

Chris.

 

[Roman]

This formula says how many blank cells are there in you range:

=SUMPRODUCT(--(ISBLANK(A7:A30)))

 

[Guest]

Chris,
Use COUNTBLANK function:

=COUNTBLANK(a7:A30)

HTH

 

[Norman Jones]

Hi Toppers,

A potential problem with the use of the COUNTBLANK worksheet function is
that cells containining formulas which resolve to "" are treated as blank.

 

[EMoe]

Hello,

Both formulas posted work great. The COUNTBLANK one is the simpliest
However, how do you add other ranges like C7:C30 to the formula wit
getting the too many arguments message?

=SUMPRODUCT(--(ISBLANK(A7:A30,C7:C30))) Doesn't work

=COUNTBLANK(a7:A30,C7:C30) Doesn't Work

Thanks,
EMo

 

[Norman Jones]

Hi EMoe,

=SUMPRODUCT(--(ISBLANK(A7:A30,C7:C30))) Doesn't work

One way:

=SUMPRODUCT(--(ISBLANK(A7:A30)--(ISBLANK(C7:C30))))

 

[Dave Peterson]

If the cells are really empty--not formulas that evaluate to "":

dim myRng as range
set myrng = worksheets("sheet1").range("a7:A30")

if myrng.cells.count > application.counta(myrng) then
'at least one empty
else
'all filled
end if

And if you want to include those formulas that evaluate to ""
(using Topper's suggestion)

dim myRng as range
set myrng = worksheets("sheet1").range("a7:A30")

if application.countblank(myrng) > 0 then
'at least one empty
else
'all filled
end if

 

[anilsolipuram]

=sumproduct(--(isblank(b7:b30))+--(isblank(c7:c30))

 

[Dave Peterson]

Or just:

=SUMPRODUCT(--(ISBLANK(B7:C30)))

But I would think that the OP wanted a programming solution--since Chris posted
in .programming.