Select first empty cell in column AND works for empty column

Question

I need to find the first blank cell in a column. The solution for this is easy assuming there are 2 or more filled cells in the column.

Range("A1").End(xlDown).Offset(1, 0).Select

This stops working if the only populated cell is A1 or if A1 is blank.

In these cases it will select the last cell in the workbook.

Is there any work around that will always select the first blank cell in the column even if that cell happens to be A1 or A2?

Answer 1

Here is a solution that tests if the cell we find is empty and if A1 is empty:

Dim Rng As Range
Set Rng = Range("A1").End(xlDown)
If Rng.Value = "" Then
    If Range("A1").Value = "" Then
        Range("A1").Select
    Else
        Range("A2").Select
    End If
Else
    Rng.Offset(1, 0).Select
End If

In the comment you write that you don't like the order of the code, here is another example:

If Range("A1").Value = "" Then
    Range("A1").Select
ElseIf Range("A2").Value = "" Then
    Range("A2").Select
Else
    Range("A1").End(xlDown).Offset(1, 0).Select
End If

And here is another example that avoids the use of End() and Offset():

Dim Cnt As Long
Cnt = ActiveSheet.UsedRange.Rows.Count
If Cnt = 1 And Range("A1").Value = "" Then Cnt = 0
Range("A" & Cnt + 1).Select

If you add a header row, then this example works:

Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Select

I always include a header row in all sheets with tabular data, to limit special cases - it's also more user friendly.

Answer 2

Find First Empty Cell by Looping

Empty

• Except looping through cells, there are various more or less reliable ways to do it.
• If there are hidden rows or columns, many of them will not work.
• Even worse, if the worksheet is filtered, probably most of them will not work.

The Basic Loop

• If you loop through the cells and test each one of them, you will surely get the correct result.

Function RefFirstEmptyCellInColumnBasic( _
    ByVal FirstCell As Range) _
As Range
    
    ' Validate the given range ('FirstCell').
    If FirstCell Is Nothing Then Exit Function
    
    ' Create a reference to the Column Range ('crg').
    With FirstCell.Cells(1)
        Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
    End With
    
    ' Loop.
    Dim cCell As Range
    ' Loop through the cells of the Column Range...
    For Each cCell In crg.Cells
        ' ... until an empty cell is found.
        If IsEmpty(cCell) Then
            ' Create a reference to the current cell.
            Set RefFirstEmptyCellInColumnBasic = cCell
            Exit Function
        End If
    Next cCell

End Function

• The issue is that it may take a long time. It will 'behave' for a few thousand rows but e.g. if the first empty cell is the last cell in the column, the previous code takes 'forever' (5s) on my machine.

Loop in Memory (Array)

• To remedy this, you can introduce an array into the previous code which will reduce the execution time ten times (0.5s). (Note that it will roughly take 0.05s each time for just writing the values to the array.)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to the top-most empty cell
'               in the one-column range from the first cell of a range
'               ('FirstCell') through the last cell in its column.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefFirstEmptyCellInColumn( _
    ByVal FirstCell As Range) _
As Range
    
    ' Validate the given range ('FirstCell').
    If FirstCell Is Nothing Then Exit Function
    
    ' Create a reference to the Column Range ('crg').
    With FirstCell.Cells(1)
        Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
    End With
    
    ' Write the values from the Column Range to the Column Data Array ('cData').
    Dim cData As Variant
    If crg.Rows.Count = 1 Then ' only one cell
        ReDim cData(1 To 1, 1 To 1): cData(1, 1) = crg.Value
    Else
        cData = crg.Value
    End If
    
    ' Loop.
    Dim r As Long
    ' Loop through the elements of the Column Data Array...
    For r = 1 To UBound(cData, 1)
        ' ... until an empty value is found.
        If IsEmpty(cData(r, 1)) Then
            ' Create a reference to the r-th cell of the Column Range.
            Set RefFirstEmptyCellInColumn = crg.Cells(r)
            Exit Function
        End If
    Next r

End Function

The Test

• To test the previous you can do the following.

Sub RefFirstEmptyCellInColumnTEST()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim fCell As Range: Set fCell = ws.Range("A3")
    ' Empty
    Dim feCell As Range: Set feCell = RefFirstEmptyCellInColumn(fCell)
    If Not feCell Is Nothing Then
        Debug.Print feCell.Address(0, 0)
    End If
End Sub

Blank

• You can do the same for blank cells i.e. empty cells or cells containing a single quote (') or cells containing formulas evaluating to "". Note that cells containing spaces are neither blank nor empty.

Function RefFirstBlankCellInColumnBasic( _
    ByVal FirstCell As Range) _
As Range ' (Empty, ="" and ')
    
    ' Validate the given range ('FirstCell').
    If FirstCell Is Nothing Then Exit Function
    
    ' Create a reference to the Column Range ('crg').
    With FirstCell.Cells(1)
        Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
    End With
    
    ' Loop.
    Dim cCell As Range
    ' Loop through the cells of the Column Range...
    For Each cCell In crg.Cells
        ' (exclude cell containing error value)
        If Not IsError(cCell) Then
            ' ... until a blank cell is found.
            If Len(cCell.Value) = 0 Then
                ' Create a reference to the current cell.
                Set RefFirstBlankCellInColumnBasic = cCell
                Exit Function
            End If
        End If
    Next cCell

End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Creates a reference to the top-most blank cell
'               in the one-column range from the first cell of a range
'               ('FirstCell') through the last cell in its column.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefFirstBlankCellInColumn( _
    ByVal FirstCell As Range) _
As Range ' (Empty, ="" and ')
    
    ' Validate the given range ('FirstCell').
    If FirstCell Is Nothing Then Exit Function
    
    ' Create a reference to the Column Range ('crg').
    With FirstCell.Cells(1)
        Dim crg As Range: Set crg = .Resize(.Worksheet.Rows.Count - .Row + 1)
    End With
    
    ' Write the values from the Column Range to the Column Data Array ('cData').
    Dim cData As Variant
    If crg.Rows.Count = 1 Then ' only one cell
        ReDim cData(1 To 1, 1 To 1): cData(1, 1) = crg.Value
    Else
        cData = crg.Value
    End If
    
    ' Loop.
    Dim r As Long
    ' Loop through the elements of the Column Data Array...
    For r = 1 To UBound(cData, 1)
        ' (exclude error values)
        If Not IsError(cData(r, 1)) Then
            ' ... until a blank is found.
            If Len(cData(r, 1)) = 0 Then
                ' Create a reference to the r-th cell of the Column Range.
                Set RefFirstBlankCellInColumn = crg.Cells(r)
                Exit Function
            End If
        End If
    Next r

End Function

Sub RefFirstBlankCellInColumnTEST()
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim fCell As Range: Set fCell = ws.Range("A3")
    ' Blank
    Dim fbCell As Range: Set fbCell = RefFirstBlankCellInColumn(fCell)
    If Not fbCell Is Nothing Then
        Debug.Print fbCell.Address(0, 0)
    End If
End Sub