Checking for empty cells in a range
 

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.

Checking for empty cells in a range
 

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

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

Checking for empty cells in a range
 

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.

Checking for empty cells in a range
 

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

Checking for empty cells in a range
 

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

--
EMo
-----------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318
View this thread: http://www.excelforum.com/showthread.php?threadid=38047

Checking for empty cells in a range
 

Hi EMoe,

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

One way:

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

---
Regards,
Norman



"EMoe" wrote in message
...

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

Checking for empty cells in a range
 

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

Checking for empty cells in a range
 

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

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=38047

Checking for empty cells in a range
 

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