Excel vba if three or more consecutive cells are blank then place value on another cell

Question

Im looking for a solution to get the result as in the screenshot. If theres at least 3 blanks or more then change second cell from first blank on first column to "x" :

Sub findThreeEmptyCells()
Dim lastRow As Long, i As Long
Dim firstEmptyCell As Range

lastRow = Cells(Rows.Count, 5).End(xlUp).Row 

For i = 10 To lastRow
If Cells(i + 1, 5).Value = "" And Cells(i + 2, 5).Value = "" Then
    Set firstEmptyCell = Cells(i + 2, 1)
    Exit For
End If
Next i

If firstEmptyCell Is Nothing Then 
MsgBox ("There are no two empty cells in a row")
Exit Sub
End If

firstEmptyCell.Value = "x"

End Sub

Does this answer your question? [If two consecutive cells are blank then second cell equals value](https://stackoverflow.com/questions/71009856/if-two-consecutive-cells-are-blank-then-second-cell-equals-value) — Solar Mike, Feb 07 '22 at 05:47
Im looking for something based on the 5th Column and minimum 3 consecutive blank cells — soldier2gud4me, Feb 07 '22 at 05:57
Then edit what you have so far. Or show how you have edited it to achieve what you now want, then people may help sort any errors. — Solar Mike, Feb 07 '22 at 06:04

score 0 · Answer 1 · answered Feb 07 '22 at 06:15

Sub findThreeEmptyCells()
Dim lastRow As Long, i As Long
Dim firstEmptyCell As Range

lastRow = Cells(Rows.Count, 5).End(xlUp).Row ' Assuming your column A has the most data or is the row you want to check.

For i = 10 To lastRow
If Cells(i + 1, 5).Value = "" And Cells(i + 2, 5).Value = "" And Cells(i + 3, 5).Value = "" Then
    Set firstEmptyCell = Cells(i + 2, 1)
    Exit For
End If
Next i

If firstEmptyCell Is Nothing Then 
MsgBox ("There are no two empty cells in a row")
Exit Sub
End If

firstEmptyCell.Value = "x"

End Sub

Excel vba if three or more consecutive cells are blank then place value on another cell

1 Answers1