Matching blank cell with MATCH function

  • Thread starter Thread starter Mae
  • Start date Start date
M

Mae

I have a list in A1:A30000. User is supposed to enter data in all cell
in the range. I dont want user to let any cell blank. I try to detec
which cell is blank. I try to use MATCH function but this functio
seems does not support blank parameter or empty string. Please help
 
Take a look at the ISBLANK() function.

To identify the row containing the first blank in A1:A30000, you could use
the following array formula:

=MIN(--(ISBLANK(A1:A30000)*ROW(A1:A30000))

Remember that you'll need to hit Ctrl-Shift-Enter after entering the formula
instead of just hitting Enter.

/i.
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayMatch("",A1:A30000,"A") will return a vertical array of the
addresses of cells with the empty string (but not blanks).

Alan Beban
 
I have a list in A1:A30000. User is supposed to enter data in all cells
in the range. I dont want user to let any cell blank. I try to detect
which cell is blank. I try to use MATCH function but this function
seems does not support blank parameter or empty string. Please help.
Click to expand...

If your range really does begin in row 1, and if it's only one column, then you
could count the number of truly blank cells using

=COUNTIF(A1:A30000,"=")

the number of either truly blank cells or cells evaluating to empty strings, "",
using

=COUNTBLANK(A1:A30000)

and the number of either truly blank cells or cells evaluating to zero or more
space characters (visually blank) using

=SUMPRODUCT(--(TRIM(A1:A30000)=""))

If you want the address of the topmost truly blank cell, you can use the array
formula

=ADDRESS(MATCH(TRUE,ISBLANK(A1:A30000),0),1)
 
Back
Top