Need a formula to locate the blank cells within the range

[Guest]

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

 

[Peo Sjoblom]

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..

 

[Guest]

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