CODEWITHSUNDEEP
excelexcel-formulamatchcell
DylanJaide
DylanJaide

Reputation: 395

Find the first empty cell in the same column/row

I am trying to work out a formula that will give me the row number of the first empty cell in a column. Currently I am using:

=MATCH(TRUE, INDEX(ISBLANK(A:A), 0, 0), 0)

This works fine, unless the formula is put in the same column as the column I am searching in, in which case it does some sort of circular reference or something. Is there a formula I can use instead which will work when placed in the same column as it searches in?

Upvotes: 11

Views: 73507

Answers (3)

user7241789
user7241789

Reputation: 43

All you have to do is count the non blank cells in a column : COUNTA e.g.

=COUNTA(E:E)

If you want to find the first blank cell from further down the column, then set the column range e.g. E23:E100 and add the cell number -1 to it.

=COUNTA(e23:E1000)+23-1

Upvotes: 4

A Curtin
A Curtin

Reputation: 61

=MATCH(TRUE,INDEX(ISBLANK(INDIRECT("R[1]C:R1048576C",0)),0,0),0)+ROW()

This formula above returns the row number of the first blank cell in the current column and below the current row, but it does not need to be entered in row 1 of the worksheet.
It replaces the A:A target range with a range that starts in the next row and continues to the end of the sheet and then adds the current row number to the result to add the skipped rows back in. This avoids the circular reference since it starts on the next row.

Using the INDIRECT(ref_text, [a1]) function with the second argument set to FALSE or 0 allows you to pass ref_text in R1C1 notation (eg. B5 as R5C2). The R1C1 notation also has a lesser known syntax for describing a location as an offset from the current position. If the number following the R or C is enclosed in square braces then it is treated as a +/- offset from the current cell or zero offset if omitted (eg. "R[-1]C[-1]" in cell B5 returns A4 and "RC" in B5 returns itself B5).
The R1C1 location of R[1]C:R1048576C from the formula defines a range starting at the current cell with a RC offset of 1,0 and ending at fixed row 1048576 with a 0 column offset from the current cell. When placed in cell C3 it will be equivalent to C4:C1048576 and in A1 is equivalent to A2:A1048576. If you needed to dynamically set the max row number, you use INDIRECT("R[1]C:R"&ROWS(A:Z)&"C",0) but since current version Excel has a row limit of 1,048,576 it makes sense to just set it.

Upvotes: 0

ejlj
ejlj

Reputation: 175

Another way to do it

=MIN(IF(A2:A6="",ROW(A2:A6)))

you have to press CTRL+SHIFT+ENTER

The difference is that this will give you the Row number of the first empty cell The previous answer will give the position (how many rows from the starting row) of the first empty cell... Both ways are valid depending on your needs

Upvotes: 5

Related Questions