Having Trouble with Macro to find first empty cell in a column

[rjsnieg]

Hello All,

I have the following code which I would like to use to Cut the contents of the active cell, find the first empty cell in a range of rows in the active column. I would like the macro to use the active column and begin searching for an empty cell between rows 22-33 (omitting any empty cells in rows 1-21). The macro below only works if row 22 is blank, it does not search the rest of the range if 22 is not empty. After the empty cell is found I would like it to paste the values and comments only which doesn't seem to be working either. Thanks for the help!

Sub Macro2()

ActiveCell.Cut
    On Error Resume Next
    Dim xCell As Range
   
    For Each xCell In ActiveSheet.Cells(22, ActiveCell.Column).Cells
        If Len(xCell) = 0 Then
            xCell.Select
            Exit For
        End If
    Next
    ActiveCell.PasteSpecial (xlPasteComments)
    ActiveCell.PasteSpecial (xlPasteValues)
End Sub

 

[smozgur]

Yes there is a possibility to have an empty cell in between values in the range.

Actually, in this case, the loop method would be the way to go. Did you have chance to try the modified code that I posted in #9?

 

[rjsnieg]

Actually, in this case, the loop method would be the way to go. Did you have chance to try the modified code that I posted in #9?

I will give this a shot in a few hours. Thanks for the help. Much appreciated!

 

[smozgur]

This is the updated first method by using the Find method.

Sub doItUpdate()
Dim rng As Range
Dim fnd As Range
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33
    
    ' Set the data entry range for Find action
    ' Note that we also include the cell before the actual range
    ' to be able to look in the first cell in the range as well (Find definition)
    Set rng = Cells(intStart - 1, ActiveCell.Column).Resize(intEnd - intStart + 2)
    ' Find the first empty cell in the entry range
    Set fnd = rng.Find(What:="", After:=rng.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
    
    ' If no empty cell is found, then alert user and exit
    If fnd Is Nothing Then
        GoTo NotFound
    ' If the cell before the actual range was empty and found it
    ElseIf fnd.Row < intStart Then
        GoTo NotFound
    End If
    ' Otherwise, we have an empty cell. Move the active cell to the empty cell
    ActiveCell.Cut fnd
    Exit Sub
NotFound:
    MsgBox "no empty cell found in the given range", vbOKOnly + vbExclamation, "no action"
End Sub

 

[footoo]

Assuming the last used row on the sheet is after row 32, here's another way :

Sub Macro2()
Dim r As Range: Set r = ActiveCell
r.Copy
On Error Resume Next
With Intersect(ActiveCell.EntireColumn, Rows("22:33")).SpecialCells(xlCellTypeBlanks)(1)
    If Err = 0 Then
        .PasteSpecial xlPasteComments
        .Value = r
        r.Clear
    Else: MsgBox "No blank cells"
    End If
End With
On Error GoTo 0
End Sub

Or perhaps this :

Sub Macro2()
On Error Resume Next
With Intersect(ActiveCell.EntireColumn, Rows("22:33")).SpecialCells(xlCellTypeBlanks)(1)
    If Err = 0 Then
        ActiveCell.Cut Range(.Address)
    Else: MsgBox "No blank cells"
    End If
End With
On Error GoTo 0
End Sub

 

[rjsnieg]

This is the updated first method by using the Find method.

Sub doItUpdate()
Dim rng As Range
Dim fnd As Range
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33
   
    ' Set the data entry range for Find action
    ' Note that we also include the cell before the actual range
    ' to be able to look in the first cell in the range as well (Find definition)
    Set rng = Cells(intStart - 1, ActiveCell.Column).Resize(intEnd - intStart + 2)
    ' Find the first empty cell in the entry range
    Set fnd = rng.Find(What:="", After:=rng.Cells(1), LookIn:=xlValues, LookAt:=xlWhole)
   
    ' If no empty cell is found, then alert user and exit
    If fnd Is Nothing Then
        GoTo NotFound
    ' If the cell before the actual range was empty and found it
    ElseIf fnd.Row < intStart Then
        GoTo NotFound
    End If
    ' Otherwise, we have an empty cell. Move the active cell to the empty cell
    ActiveCell.Cut fnd
    Exit Sub
NotFound:
    MsgBox "no empty cell found in the given range", vbOKOnly + vbExclamation, "no action"
End Sub

smozgur, this works great!

 

[smozgur]

smozgur, this works great!

Great to hear that it works.

@footoo's method using SpecialCells is also very good one. As footoo also explained, The only requirement is that the SpecialCells method uses the used range to make the search, so if the data entry range (22:33) is not in the used range area in the worksheet, then SpecialCells won't look in that area.