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

 

[footoo]

There is only one cell (row 22) in "ActiveSheet.Cells(22, ActiveCell.Column)".

You have to copy something before you can paste.

 

[rjsnieg]

Thanks footoo. I apologize for my ignorance. I thought by having the "ActiveCell.Cut before the "On Error Resume next" would have taken care of the copying problem. Is there a way to list a range of rows, or loop through until it finds an empty cell in the range?

 

[smozgur]

Welcome to the board!

Please try the following to see if it helps. It looks like a long and complicated code but most of it are comments and it hopefully is not that complicated with the comments.

Sub doIt()
Dim rng As Range
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33

    ' Define the beginning cell for searching
    Set rng = Cells(intStart - 1, ActiveCell.Column)
    ' Making sure the very first cell is not empty
    ' Otherwise .End method will skip the correct empty cell
    If Not IsEmpty(rng.Offset(1)) Then
        Set rng = rng.Cells(1, 1).End(xlDown)
    End If
    ' So, we are sure the next cell is empty
    Set rng = rng.Offset(1)
    ' If it is out of the allowed empty cells range, then do not copy
    ' So you can remove the MsgBox to not alert the user.
    If rng.Row > intEnd Then
        MsgBox "No empty cell in rows " & intStart & "-" & intEnd, vbOKOnly + vbExclamation, "No action"
        Exit Sub
    End If
    ' We got the correct cell to move the active cell's content
    ActiveCell.Cut rng
End Sub

 

[rjsnieg]

Welcome to the board!

Please try the following to see if it helps. It looks like a long and complicated code but most of it are comments and it hopefully is not that complicated with the comments.

Sub doIt()
Dim rng As Range
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33

    ' Define the beginning cell for searching
    Set rng = Cells(intStart - 1, ActiveCell.Column)
    ' Making sure the very first cell is not empty
    ' Otherwise .End method will skip the correct empty cell
    If Not IsEmpty(rng.Offset(1)) Then
        Set rng = rng.Cells(1, 1).End(xlDown)
    End If
    ' So, we are sure the next cell is empty
    Set rng = rng.Offset(1)
    ' If it is out of the allowed empty cells range, then do not copy
    ' So you can remove the MsgBox to not alert the user.
    If rng.Row > intEnd Then
        MsgBox "No empty cell in rows " & intStart & "-" & intEnd, vbOKOnly + vbExclamation, "No action"
        Exit Sub
    End If
    ' We got the correct cell to move the active cell's content
    ActiveCell.Cut rng
End Sub

This worked perfectly. I appreciate the help!

 

[smozgur]

And the loop version (which actually makes more sense but is slower than the first one - of course, the slowness doesn't matter for this but is a good habit to consider that for a different scenario like looping through thousands of cells).

Sub doItWithLoop()
Dim i As Integer
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33
   
    ' Looping in the allowed cells
    For i = intStart To intEnd
        If IsEmpty(Cells(i, ActiveCell.Column)) Then
            ActiveCell.Cut Cells(i, ActiveCell.Column)
        End If
    Next i
End Sub

 

[smozgur]

This worked perfectly. I appreciate the help!

You're welcome. Glad to hear it helps.

Note: If you receive a working answer for your question, then please consider marking that post as the solution to help future readers have a similar question. You can read Mark as Solution for more information.

 

[rjsnieg]

Welcome to the board!

Please try the following to see if it helps. It looks like a long and complicated code but most of it are comments and it hopefully is not that complicated with the comments.

Sub doIt()
Dim rng As Range
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33

    ' Define the beginning cell for searching
    Set rng = Cells(intStart - 1, ActiveCell.Column)
    ' Making sure the very first cell is not empty
    ' Otherwise .End method will skip the correct empty cell
    If Not IsEmpty(rng.Offset(1)) Then
        Set rng = rng.Cells(1, 1).End(xlDown)
    End If
    ' So, we are sure the next cell is empty
    Set rng = rng.Offset(1)
    ' If it is out of the allowed empty cells range, then do not copy
    ' So you can remove the MsgBox to not alert the user.
    If rng.Row > intEnd Then
        MsgBox "No empty cell in rows " & intStart & "-" & intEnd, vbOKOnly + vbExclamation, "No action"
        Exit Sub
    End If
    ' We got the correct cell to move the active cell's content
    ActiveCell.Cut rng
End Sub

Hey smozgur, I ran this and it appears to search for the first row being empty then it overwrites the 2nd row (row 23) whether or not it is empty. I think it has to do with the

If Not IsEmpty(rng.Offset(1)) Then
Set rng = rng.Cells(1, 1).End(xlDown)

How do I tell it to keep going if the subsequent rows are empty?

 

[smozgur]

Yes, I now realize that it fails. In fact, even the loop sample I sent has the missing loop break and it fails as well.

For a quick solution, here is the fixed loop version. It should work without problems.

Sub doItWithLoop()
Dim i As Integer
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33
   
    ' Looping in the allowed cells
    For i = intStart To intEnd
        If IsEmpty(Cells(i, ActiveCell.Column)) Then
            ActiveCell.Cut Cells(i, ActiveCell.Column)
            Exit For
        End If
    Next i
End Sub

For the first approach, could you please tell me if it is possible to have a blank cell in the row 22:33 range in between? I mean, do you edit cells in 22:33 manually, so maybe there might be something entered in row 26 but row 24 could be empty? Or, similarly, maybe you delete a cell value in the row 22:33 range? Basically, is the following situation ever possible? (See D23 is empty. Should the next empty cell be D23 OR D25 in this case - OR is this not even expected?)

Book3
	D
1	value1
2	value2
3	value3
4	value4
5	value5
6	value6
7	value7
8	value8
9	value9
10
11	value11
12	value12
13	value13
14	value14
15
16	value16
17
18	value18
19	value19
20	value20
21	value21
22	value10
23
24	value17
25
26
27
28
29
30
31
32
33
Sheet1

If not, then I will approach finding the empty cell from the bottom of the 22:33 range.

 

[rjsnieg]

Yes, I now realize that it fails. In fact, even the loop sample I sent has the missing loop break and it fails as well.

For a quick solution, here is the fixed loop version. It should work without problems.

Sub doItWithLoop()
Dim i As Integer
Dim intStart As Integer
Dim intEnd As Integer

    ' Defining the cell row numbers for allowed empty cells
    intStart = 22
    intEnd = 33
  
    ' Looping in the allowed cells
    For i = intStart To intEnd
        If IsEmpty(Cells(i, ActiveCell.Column)) Then
            ActiveCell.Cut Cells(i, ActiveCell.Column)
            Exit For
        End If
    Next i
End Sub

For the first approach, could you please tell me if it is possible to have a blank cell in the row 22:33 range in between? I mean, do you edit cells in 22:33 manually, so maybe there might be something entered in row 26 but row 24 could be empty? Or, similarly, maybe you delete a cell value in the row 22:33 range? Basically, is the following situation ever possible? (See D23 is empty. Should the next empty cell be D23 OR D25 in this case - OR is this not even expected?)

Book3
	D
1	value1
2	value2
3	value3
4	value4
5	value5
6	value6
7	value7
8	value8
9	value9
10
11	value11
12	value12
13	value13
14	value14
15
16	value16
17
18	value18
19	value19
20	value20
21	value21
22	value10
23
24	value17
25
26
27
28
29
30
31
32
33
Sheet1

If not, then I will approach finding the empty cell from the bottom of the 22:33 range.

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