first blank i a column

[rog]

Hi, I need a routine to search for the first blank cell in a column.
Thanks

 

[Bob Phillips]

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)))

 

[rog]

Thanks Bob, but how to do it in VBA?

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)))

 

[Bob Phillips]

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