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

[TheIrishThug]

very nice, thank you

[Rowan Drummond]

You're welcome

TheIrishThug wrote:
> very nice, thank you
>
>

[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.

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