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

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

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