Find next empty cell with Excel VBA
When you write VBA macros in Excel, you often need to find the next empty cell in a column or a row. This could be for inserting new data, for defining a range or for some other purpose.
Here is a function that does just that. The function is called by a procedure and is told what cell to start with. The function returns the next empty cell as a range.
Public Function FindNextEmpty(ByVal rCell As Range) As Range
On Error GoTo ErrorHandle
With rCell
If Len(.Formula) = 0 Then
Set FindNextEmpty = rCell
ElseIf Len(.Offset(1, 0).Formula) = 0 Then
Set FindNextEmpty = .Offset(1, 0)
Else
Set FindNextEmpty = .End(xlDown).Offset(1, 0)
End If
End With
Exit Function
ErrorHandle:
MsgBox Err.Description & ", Function FindNextEmpty."
End Function
Of course the function can easily be changed, so that instead of looking down it can look left, right or up. You just change the "Offset". (1,0) is the row below, (0,1) is the column to the right, (0,1) is the column to the left and (1,0) is the row above.
Take care that you dont point to the column to the left .Offset(0,1), if you are in column A  that will trigger an error. Besides Offset you have to change (xlDown) to (xlToRight), (xlToLeft) or (xlUp).
The procedure below is an example of how to call the function FindNextEmpty. It passes cell A1 as start cell. To test, highlight the code with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module. Then run the procedure Sub TestEmpty().
Sub TestEmpty()
Dim rCell As Range
Set rCell = FindNextEmpty(Range("A1"))
rCell.Value = "Filled by macro"
MsgBox rCell.Address & " " & rCell.value
Set rCell = Nothing
End Sub
Find next empty cell as an offset
The following function is just like FindNextEmpty, it just returns an offset ("distance") from the start cell instead of a range object.
Public Function NextEmptyRow(ByVal rCell As Range) As Integer
On Error GoTo ErrorHandle
If Len(rCell.Formula) = 0 Then
NextEmptyRow = 0
ElseIf Len(rCell.Offset(1, 0).Formula) = 0 Then
NextEmptyRow = 1
Else
Set rCell = Range(rCell.Offset(0, 0), rCell.Offset(0, 0).End(xlDown))
NextEmptyRow = rCell.Rows.Count + 1
End If
BeforeExit:
Set rCell = Nothing
Exit Function
ErrorHandle:
MsgBox Err.Description & ", function NextEmptyRow"
Resume BeforeExit
End Function
To test the function, copy it and the following procedure to a VBA module and run the procedure "TestOffset". The procedure uses cell A1 as start cell, so write something in A1 and some of the cells below.
Sub TestOffset()
Dim i As Integer
i = NextEmptyRow(Range("A1"))
MsgBox "Offset is " & i
End Sub
Related:
