first blank i a column
 

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

first blank i a column
 

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

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


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

first blank i a column
 

Thanks Bob, but how to do it in VBA?

"Bob Phillips" wrote in message
...
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)))

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


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

first blank i a column
 

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

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"rog" wrote in message
...
Thanks Bob, but how to do it in VBA?

"Bob Phillips" wrote in message
...
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)))

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


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