Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to locate the blank cells within the range
Hi,
I have a huge range of cells in which the data is entered manually. I need to cross check whether the data has been entered in all the cells in that particular range. I had used the formula = COUNTBLANK(range) to get the number of blank cells present in that range. I need a formula (If any) to locate the blank cells within the range. If suppose there are 4 blank cells within the range I need the location like A10, B20, C22, D43... Sasikiran |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to locate the blank cells within the range
Why would you need a formula, why not just use autofilter and filter one
column at a time and select blanks? To get the cell address of blank cells would require multiple array formulas one way or the other and if the data to check is huge as you say it might seriously slow down the workbook to a standstill.. -- Regards, Peo Sjoblom "Sasikiran" wrote in message ... Hi, I have a huge range of cells in which the data is entered manually. I need to cross check whether the data has been entered in all the cells in that particular range. I had used the formula = COUNTBLANK(range) to get the number of blank cells present in that range. I need a formula (If any) to locate the blank cells within the range. If suppose there are 4 blank cells within the range I need the location like A10, B20, C22, D43... Sasikiran |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need a formula to locate the blank cells within the range
You can either use:
Edit Goto Special Blanks or this samll UDF: Function addrsblank(r As Range) As String addrsblank = "" For Each rr In r If IsEmpty(rr) Then addrsblank = addrsblank & rr.Address End If Next End Function -- Gary''s Student - gsnu200731 "Sasikiran" wrote: Hi, I have a huge range of cells in which the data is entered manually. I need to cross check whether the data has been entered in all the cells in that particular range. I had used the formula = COUNTBLANK(range) to get the number of blank cells present in that range. I need a formula (If any) to locate the blank cells within the range. If suppose there are 4 blank cells within the range I need the location like A10, B20, C22, D43... Sasikiran |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill Blank cells in a range | Excel Discussion (Misc queries) | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Sorting and Eliminating Blank Cells in Formula range | Excel Worksheet Functions | |||
Remove blank cells from a range | Excel Discussion (Misc queries) | |||
How do I skip blank cells when copying over a range of cells? | Excel Discussion (Misc queries) |