first empty cell

[TheIrishThug]

starting at A3 i need to return the first cell that is empty.

i tried:
Row = 3
While IsEmpty("A" & Row) = False
Row = Row + 1
Wend

but i get a overflow error that selects "row=row+1"

i found the following code somewhere else, but this won't work. the
sheet i'm using is setup as a list. so the last line of the list says
"total." since the following looks from the bottom up, it does not go
to possible empty cells above that.

code:
Sub FindLastCell()
Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With
MsgBox LastCell.Row
End Sub

 

[Rowan Drummond]

Try:

Sub EmptyCell()
Dim eCell As Range
If IsEmpty(Range("A4")) Then
Set eCell = Range("A4")
Else
Set eCell = Range("A3").End(xlDown).Offset(1, 0)
End If
MsgBox eCell.Address
End Sub

Hope this helps
Rowan

 

[TheIrishThug]

very nice, thank you

 

[Rowan Drummond]

You're welcome

 

[TheIrishThug]

i'm getting an error with this function now. i tweaked it to a little
and now have:

Function EmptyCell(col As String, startRow As Integer)
Dim eCell As Range
'If Col,startRow is empty return startRow
'If Col,startRow is not empty search down and return row of first empty
cell
If IsEmpty(Range(col & startRow)) Then
Set eCell = Range(col & startRow)
Else
Set eCell = Range(col & startRow).End(xlDown).Offset(1, 0)
End If
EmptyCell = eCell.row
End Function

in this case i am passing col="A" and startRow=3
it works when the function is called with A3 empty, but then when it
should be returning A4 as the next empty cell. i get a
"application-defined or user defined error" and Debug highlights the
line "Set eCell = Range(col & startRow).End(xlDown).Offset(1, 0)"

 

[Dave Peterson]

How about:

Option Explicit
Function EmptyCell(col As String, startRow As Long)
Dim eCell As Range
'If Col,startRow is empty return startRow
'If Col,startRow is not empty search down and return row of first empty cell
If IsEmpty(Range(col & startRow)) Then
Set eCell = Range(col & startRow)
ElseIf IsEmpty(Range(col & startRow + 1)) Then
Set eCell = Range(col & startRow + 1)
Else
Set eCell = Range(col & startRow).End(xlDown).Offset(1, 0)
End If
EmptyCell = eCell.Row
End Function

I changed the StartRow to Long.