Get row number of first empty cell in column and store that value in other cell

Question

I want to find row number of first empty cell in column and store that row number in Cell Z1.

I tried with Following macro code but it goes into loop forever. As soon as it tries to set the value in Cell Z1 it again goes into worksheet_change event again and then again in for loop.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
    Set ws = ActiveSheet
    For Each cell In ws.Columns(3).Cells
        If IsEmpty(cell) = True Then Range("$Z$1").Value = cell.Row: Exit For
      Next cell
End Sub

Please help to resolve this.

Thanks

Answer 1

Maybe this code is of any help

    Option Explicit

    Function firstEmptyCell(col As Long, Optional ws As Worksheet) As Range

    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If

    Dim rg As Range
    Set rg = ws.Cells(1, col)

    If Len(rg.Value) = 0 Then
        Set rg = rg.Offset
    Else
        If Len(rg.Offset(1).Value) = 0 Then
            Set rg = rg.Offset(1)
        Else
            Set rg = rg.End(xlDown)
            Set rg = rg.Offset(1)
        End If
    End If
    Set firstEmptyCell = rg

End Function

And the Event code is

    Private Sub Worksheet_Change(ByVal Target As Range)

        On Error GoTo EH

        If Target.Column <> 12 Then
            Exit Sub
        End If

        Application.EnableEvents = False
        Range("Z1").Value = firstEmptyCell(12).Row

    EH:
        Application.EnableEvents = True
    End Sub

Update: Based on the comments regarding the pitfalls of the change event one could change firstEmptyCell slightly and use a UDF only

Function firstEmptyCellA(col As Long, Optional ws As Worksheet) As Long

    On Error GoTo EH
    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If

    Application.Volatile

    Dim rg As Range
    Set rg = ws.Cells(1, col)

    If Len(rg.Value) = 0 Then
        Set rg = rg.Offset
    Else
        If Len(rg.Offset(1).Value) = 0 Then
            Set rg = rg.Offset(1)
        Else
            Set rg = rg.End(xlDown)
            Set rg = rg.Offset(1)
        End If
    End If
    firstEmptyCellA = rg.Row
    Exit Function
EH:
    firstEmptyCellA = 0

End Function

Answer 2

Tricky Enable Events

This is triggered only when a cell in the 12th column (L) is changed, otherwise there is no need for it. If you have formulas there, then this will not work and you'll have to use the Worksheet_Calculate event.

Row of First Empty Cell in Column

Option Explicit

' Row of First Empty Cell in Column
Private Sub Worksheet_Change(ByVal Target As Range)

    Const TargetCell As String = "Z1"
    Const TargetColumn As Variant = 12   ' (or "L")
    Dim rng As Range

    If Intersect(Columns(TargetColumn), Target) Is Nothing Then Exit Sub

    Application.EnableEvents = False
        Set rng = Columns(TargetColumn).Find(What:="", _
          After:=Cells(Rows.Count, TargetColumn), LookIn:=xlValues)
        If rng Is Nothing Then
            Range(TargetCell).Value = 0  ' Full column. No empty cells.
        Else
            Range(TargetCell).Value = rng.Row
        End If
    Application.EnableEvents = True

End Sub

Row of First Empty Cell After Last Non-Empty Cell in Column

Option Explicit

' Row of First Empty Cell After Last Non-Empty Cell in Column
Private Sub Worksheet_Change(ByVal Target As Range)

    Const TargetCell As String = "Z1"
    Const TargetColumn As Variant = 12   ' (or "L")
    Dim rng As Range

    If Intersect(Columns(TargetColumn), Target) Is Nothing Then Exit Sub

    Application.EnableEvents = False
        Set rng = Columns(TargetColumn).Find(What:="*", LookIn:=xlFormulas, _
          SearchDirection:=xlPrevious)
        If rng Is Nothing Then           ' Empty column. No non-empty cells.
            Range(TargetCell).Value = 1
        Else
            If rng.Row = Rows.Count Then ' Last (bottom-most) cell is not empty.
                Range(TargetCell).Value = 0
            Else
                Range(TargetCell) = rng.Offset(1).Row
            End If
        End If
    Application.EnableEvents = True

End Sub

Answer 3

Dont need a loop. Paste this in a module not in a worksheet event unless you want it for every worksheet change.

Sub Macro1()
    ActiveSheet.Range("Z1") = ActiveSheet.Columns(3).SpecialCells(xlCellTypeBlanks)(1).Row
End Sub

if you want it after every change then put it in a worksheet as. This code will not run everytime. It will check if Z1 is empty then enter the valu. Then if Z1 is not empty it will check if the target cell is in column C

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = ActiveSheet.Columns(3)

If IsEmpty(Range("Z1")) Then
    Range("Z1") = Rng.SpecialCells(xlCellTypeBlanks)(1).Row
Else
If Not Intersect(Range("C1:C" & Range("Z1").Value), Target) Is Nothing Then
    Range("Z1") = Rng.SpecialCells(xlCellTypeBlanks)(1).Row
End If
End If
End Sub