Finding the last empty cell in a column - formula

[FaneDuru]

1. I use: "=MATCH(1E+306,A:A)" in order to find the last value in a A:A column.
2. I use: "=MATCH("*",A:A,-1)" in order to find the last text value in A:A column.

Is it a way (formula) able to find the next empty cell in a column? I need an Excel formula. With VBA it is easy but I need that for a little complicate formula able to sumarize some data in areas separated with empty lines...

Fane Duru

 

[bluedragon2]

Use a little logic with what you have:

=IF(MATCH("*",A:A,-1)>MATCH(1E+306,A:A),MATCH("*",A:A,-1)+1,MATCH(1E+306,A:A)+1)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[FaneDuru]

Thanks for your reply bluedragon2!
Maybe I was not so clear in what I need....
If in an Excel sheet in column A1:A10 there are the next records: from A1 to A5 in all the cells are values, A6 is empty and all the next cells contain values I need a formula working on the range A1:A10 and pointing to the first empty cell in this range, respectively A6.
How is this possible?

Fane Duru

 

[mbarron]

You can create a function to find the first blank cell.


Function blank(myRange As Range)

For Each myRange In myRange
If myRange = "" Then
blank = myRange.Row
Exit Function
Else
blank = myRange.Row
End If

Next
End Function

Mike

 

[bluedragon2]

My apoloigies, to my limited knowledge, there is not a ready function for such a task.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[macropod]

Hi Duru,

Try:
=MIN(IF(((A1:A10)=0)*ISBLANK(A1:A10),ROW(A1:A10)))
entered as an array formula (ie Ctrl-Shift-Enter).

Cheers

 

[KenWright]

Don't think you need that first bit do you M?

=MIN(IF(ISBLANK(A1:A10),ROW(A1:A10)))

seems to work fine for me.

FD, if you want to use what Macropods formula gives you to get the address, then simply concatenate the column label with the formula, eg:-

="A"&MIN(IF(ISBLANK(A1:A10),ROW(A1:A10)))

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[FaneDuru]

Thanks guys,
I need a formula able to sumarize the values in A:A column from the active row to the next empty cell. If I use the formula:
{=SUM(INDIRECT("A"&ROW()&":A"&MIN(IF(ISBLANK(A2:A$100),ROW(A2:A$100)))))}
it works very well. If I copy it in an other row it is adapted by Excel and works, too.
But I tried to find a general formula which I may write from VBA, able to work in any cell I put it:
{=SUM(INDIRECT("A"&ROW()&":A"&MIN(IF(ISBLANK(INDIRECT("A"&ROW()&":A$100")),ROW(INDIRECT("A"&ROW()&":A$100"))))))}
It doesn't work even if:
{=SUM(INDIRECT("A"&ROW()&":A"&MIN(IF(ISBLANK(INDIRECT("A"&2&":A$100")),ROW(INDIRECT("A"&2&":A$100"))))))}
works fine...
It looks that Row() is not interpreted in the INDIRECT Function. Any idea?

Thanks in advance,
Fane Duru

 

[GlennUK]

The ROW function works fine within an INDIRECT function, as you can see from your last example, which starts:
{=SUM(INDIRECT("A"&ROW()&":
it's just that your other usage of ROW() is within an array contruct that's trying to operate over a range of rows, and that's what ROW seems to be having a problem with.

As you are writing this from within VBA, you ought to change the references to ROW() to be VBA variables converted into strings embedded into the full string of your formula ... for example Activecell.Row might fit the bill.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[macropod]

Another approach might be to use something like:
{=MIN(IF(ISBLANK(A1:OFFSET(A$1,MATCH(1E+306,A:A)-1,))*(ROW()<MATCH(1E+306,A:A)),ROW(A1:OFFSET(A$1,MATCH(1E+306,A:A)-1,))))}
As you'd expect, if this formula is put in row 1 and copy it down, the two relative row references will change. The result of this is that, if column A on the current row is blank, and it's not the last-used row, then the current row will be reported. If you're below the last blank row before the last-used row or you're at or below the last-used row, you'll get 0.

If you change the formula to:
{=MIN(IF(ISBLANK(A$1:OFFSET(A$1,MATCH(1E+306,A:A)-1,))*(ROW()<MATCH(1E+306,A:A)),ROW(A$1:OFFSET(A$1,MATCH(1E+306,A:A)-1,))))}
to convert the two relative row references to absolutes, the result will be that, if column A on the current row is blank or is past the last blank row, and it's not the last-used row, then the first blank row in the column will be reported. If you're at or below the last-used row, you'll get 0.

Extending this to:
{=MIN(IF(ISBLANK(A$1:OFFSET(A$1,MATCH(1E+306,A:A)-1,))*(ROW()<MATCH(1E+306,A:A))*(COUNTIF(A$1:A1,"")<2),ROW(A$1:OFFSET(A$1,MATCH(1E+306,A:A)-1,))))}
will return the same result as the previous formula, except that you'll get 0s in all rows past the first blank row.

Of course, changing the column refernces to absolutes will allow the formulae to work on column A regardless of whichever other column their used in.

Hope one or more of these helps.

Cheers