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
 

1. Select the range (say A7:A30)
2. Apply autofilter
3. Click down-arrow at Cell A7
4. Check in the list if there is the word "empty"
5. Clicking "empty" you will see the empty cells

Regards,
Stefi


€žChris Strug€� ezt Ã*rta:

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
 

Sub check_cells()

Dim cell As Range
Dim a As Long

For Each cell In Range("a1:b10")
If cell.Value = "" Then a = a + 1
Next cell

MsgBox "Number of empty cells in range: " & a

End Sub


--
sihvonma
------------------------------------------------------------------------
sihvonma's Profile: http://www.excelforum.com/member.php...o&userid=16511
View this thread: http://www.excelforum.com/showthread...hreadid=380478

Checking for empty cells in a range
 

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

could be all right for you if the ranges are the same size, note that

=SUMPRODUCT(--(ISBLANK(A7:A30)--(ISBLANK(C7:C35)))) doesn´t work.

Then it´s OK to use

=SUMPRODUCT(--(ISBLANK(A7:A30)))+SUMPRODUCT(--(ISBLANK(C7:C35)))

Enjoy!