first blank i a column

  • Thread starter Thread starter rog
  • Start date Start date
Rog,

This gives you the row of the first blank

=MIN(IF(ISBLANK(A1:A1000),ROW(1:1000)))

change the column and number of rows to suit

it's an array formula, so enter with Ctrl-Shift-Enter. To get the cell, just
precede with your column letter, A in my example
=A&=MIN(IF(ISBLANK(A1:A1000),ROW(1:1000)))
 
Thanks Bob, but how to do it in VBA?

Bob Phillips said:
Rog,

This gives you the row of the first blank

=MIN(IF(ISBLANK(A1:A1000),ROW(1:1000)))

change the column and number of rows to suit

it's an array formula, so enter with Ctrl-Shift-Enter. To get the cell, just
precede with your column letter, A in my example
=A&=MIN(IF(ISBLANK(A1:A1000),ROW(1:1000)))
Click to expand...
 
Rog,

You could use that formula, or you could just loop through looking for an
empty cell

For i =1 to Cells(Rows.Count,"A").End(xlUp).Row
if IsEmpty(Cells(i,"A").Value) Then
'i is the row
Exit For
End If
Next

This will pass over a formula that returns an empty cell, so if you want to
find that one, use

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A").Value = "" Then
'i is the row
Exit For
End If
Next
 
Back
Top