Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for empty cells in a range
This formula says how many blank cells are there in you range:
=SUMPRODUCT(--(ISBLANK(A7:A30))) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking Range of Cells on Multiple Worksheets in the same workboo | Excel Discussion (Misc queries) | |||
Checking for empty cells in a range | Excel Worksheet Functions | |||
Evaluating a Range of Empty Cells with VBA | Excel Programming | |||
sum next two non-empty cells in a range | Excel Worksheet Functions | |||
Plus range of cells from different sheets if not empty | Excel Programming |