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)))

[Toppers]

Chris,
Use COUNTBLANK function:

=COUNTBLANK(a7:A30)

HTH

"Chris Strug" wrote:

> 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.
>
>
>
>

[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 with getting the too many arguments message?

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

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

Thanks,
EMoe

[Norman Jones]

Hi EMoe,

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

One way:

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

---
Regards,
Norman



"EMoe" <[email protected]> wrote in message
news:[email protected]...
>
> 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 with
> getting the too many arguments message?
>
> =SUMPRODUCT(--(ISBLANK(A7:A30,C7:C30))) Doesn't work
>
> =COUNTBLANK(a7:A30,C7:C30) Doesn't Work
>
> Thanks,
> EMoe
>
>
> --
> EMoe
> ------------------------------------------------------------------------
> EMoe's Profile:
> http://www.excelforum.com/member.php...o&userid=23183
> View this thread: http://www.excelforum.com/showthread...hreadid=380477
>

[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.

---
Regards,
Norman



"Toppers" <[email protected]> wrote in message
news:[email protected]...
> Chris,
> Use COUNTBLANK function:
>
> =COUNTBLANK(a7:A30)
>
> HTH
>
> "Chris Strug" wrote:
>
>> 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.
>>
>>
>>
>>

[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


Chris Strug wrote:
>
> 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.

--

Dave Peterson

[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.

anilsolipuram wrote:
>
> =sumproduct(--(isblank(b7:b30))+--(isblank(c7:c30)))
>
> --
> anilsolipuram
> ------------------------------------------------------------------------
> anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
> View this thread: http://www.excelforum.com/showthread...hreadid=380477

--

Dave Peterson