tusharm
MrExcel MVP
- Joined
- May 28, 2002
- Messages
- 11,029
A popular way to find the first empty cell starting with a predetermined cell is to use the End method of the Range object. For example, to find the first empty cell going down a column, one would use StartCell.End(xlDown).
However, the End property has some serious limitations. If the StartCell is empty, or if it is the only cell with data (in the direction of 'travel'), or if it is already at the worksheet boundary (again, in the direction of 'travel'), the results of the End property will be wrong or a fault will occur.
The enhanced findFirstEmpty addresses those concerns. Comments on the code are welcome -- as are improvements.
Edit: Corrected errors noted by fellow Microsoft Excel MVP John Green regarding the use of xlLeft and xlRight rather than the recommended xlToLeft and xlToRight.
However, the End property has some serious limitations. If the StartCell is empty, or if it is the only cell with data (in the direction of 'travel'), or if it is already at the worksheet boundary (again, in the direction of 'travel'), the results of the End property will be wrong or a fault will occur.
The enhanced findFirstEmpty addresses those concerns. Comments on the code are welcome -- as are improvements.
Code:
Function findFirstEmpty(startCell As Range, MoveDirection As Long) _
As Range
On Error Resume Next
'The above traps boundary conditions and returns an empty range _
A boundary condition is the StartCell being at the extreme _
position in the direction specified or the row/column being _
full in the specified direction
If IsEmpty(startCell) Then
Set findFirstEmpty = startCell
Else
Select Case MoveDirection
Case xlDown
Set findFirstEmpty = _
IIf(IsEmpty(startCell.Offset(1, 0)), _
startCell.Offset(1, 0), _
startCell.End(xlDown).Offset(1, 0))
Case xlUp
Set findFirstEmpty = _
IIf(IsEmpty(startCell.Offset(-1, 0)), _
startCell.Offset(-1, 0), _
startCell.End(xlUp).Offset(-1, 0))
Case xlToRight
Set findFirstEmpty = _
IIf(IsEmpty(startCell.Offset(0, 1)), _
startCell.Offset(0, 1), _
startCell.End(xlToRight).Offset(0, 1))
Case xlToLeft
Set findFirstEmpty = _
IIf(IsEmpty(startCell.Offset(0, -1)), _
startCell.Offset(0, -1), _
startCell.End(xlToLeft).Offset(0, -1))
End Select
End If
End Function