Home > Excel VBA macros > Find next empty cell

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
'Finds the first empty cell downwards in a column.

On Error GoTo ErrorHandle

With rCell
   'If the start cell is empty it is the first empty cell.
   If Len(.Formula) = 0 Then
      Set FindNextEmpty = rCell
      'If the cell just below is empty
   ElseIf Len(.Offset(1, 0).Formula) = 0 Then
      Set FindNextEmpty = .Offset(1, 0)
   Else
      'Finds the last cell with content.
      '.End(xlDown) is like pressing CTRL + arrow down.
      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

'We call the function and tell it to start the search in cell A1.
Set rCell = FindNextEmpty(Range("A1"))

'Shows a message box with the cell address. Right here is where
'you write the code that uses the empty cell.
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

'Finds the first empty cell (downwards).
'The input (start cell) is rCell, and the function returns the offset
'("distance") tto the first empty cell. If the start cell is empty,
'the function returns 0 (zero).

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: