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


--
TheIrishThug
------------------------------------------------------------------------
TheIrishThug's Profile: http://www.excelforum.com/member.php...o&userid=29682
View this thread: http://www.excelforum.com/showthread...hreadid=494788

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 wrote:
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

very nice, thank you


--
TheIrishThug
------------------------------------------------------------------------
TheIrishThug's Profile: http://www.excelforum.com/member.php...o&userid=29682
View this thread: http://www.excelforum.com/showthread...hreadid=494788

You're welcome

TheIrishThug wrote:
very nice, thank you

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


--
TheIrishThug
------------------------------------------------------------------------
TheIrishThug's Profile: http://www.excelforum.com/member.php...o&userid=29682
View this thread: http://www.excelforum.com/showthread...hreadid=494788

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.

TheIrishThug wrote:

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

--
TheIrishThug
------------------------------------------------------------------------
TheIrishThug's Profile: http://www.excelforum.com/member.php...o&userid=29682
View this thread: http://www.excelforum.com/showthread...hreadid=494788

--

Dave Peterson