Display the line number of the first unused row

[mistawicket]

Hello!
New to the site & the search option didn't help me find what I was looking for...

Is there a simple formula I can enter in a cell to display the first blank line I have in a range of B6:B2000, without using a macro?

 

[PaddyD]

hi - welcome to the board.

=MIN(IF(LEN(A1:A10)=0,ROW(A1:A10)))

..entered with control + shift + enter, not just enter.

 

[mistawicket]

Thnx for the welcome... Wow that was a fast response...

so If I use what you posted...

"
=MIN(IF(LEN(A1:A10)=0,ROW(A1:A10)))
"

but replace the range with my data:

=MIN(IF(LEN(B6:B2000)=0,ROW(B6:B2000)))

then it should show the first unused cell & not the last empty cell?



Gravy! I tried it & it worked...! Thnx for the info!

Out of curiosity, why do you have to enter this formula while holding crtl + shift?

 

[PaddyD]

check out the excel help topic on 'array formulas & how to enter them' (or something like that)

 

[mistawicket]

will do... Thnx again!

 

[Yogi Anand]

Hi mistawicket:

Welcome to MrExcel Board!

Try the following array formula ...

=MIN(IF(B6:B2000="",ROW(B6:B2000)))

an array formula is entered with CTRL+SHIFT+ENTER rather than with just ENTER

 

[mistawicket]

Thanks Yogi! Thar formula worked too... Kinda crazy how there are multiple ways to do the same thing...!