Finding empty cells 2

[ab123456]

I have a range of data but within this range there are some empty cells, some cells containing text and others with numbers. How can I find the first empty cell within each column? Say my data was in range A1:B25 wth cells A3 and B15 empty I want cell A26 to display 3 and B26 to display 15.

I can do this using VB but i want to try and get away from this solution if possible. The spreadsheet will be used by others so i dont want to rely on them having to run a macro and as the sheet is 10000's line long and approx 10 column there is a noticeable delay for the macro to run so i dont want to autmomatically run the macro each time the sheet is updated.

 

[joerd]

I think this is a classic, but I forgot who gave me the solution, so I can give no proper credit.
If you enter:
[tt]=MATCH("",IF(ISBLANK(A1:A25),"",A1:A25),0)[/tt]
in cell [A26], and then hit Ctrl-Enter (enter as array formula), you should get your desired result. Of course, it might be better to use a named range to refer to your data.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yakpol]

Excel has a special command to find first blank cell in a range. Select range, Edit-Goto-Special-blanks. To shorten the keying you can record a macro or use F5 for GOto command.

 

[pmover]

a star for joerd and yakpol!

 

[dagur777]

Go to "Data", "Filter", "AutoFilter", select "(Custom...)", show row where: equal (= blank, do not type anything), it will filter out all empty cell. Then copy the whole worksheet to another worksheet, you can get what you want.