[FIXED] How to check if empty cell in a range?

Issue

I just want to check if there are empty rows in a range, for instance, if S28 is either "KO" or "OK", the line above (offset(-1,0) should not be blank.
If it is blank the function should stop.
If a cell is blank and the cell above is blank, that is ok.

Each cell in S has a formula, countif function.

The code says that there are empty rows, which is not the case. I removed the data in S28, which you can see on the picture. Hence, there should be no msgbox. The first line check is in S12.

Private Function detecht_empty_rows() As Boolean
    
    Call DefineVariables
    
    Dim lrowS As Long
    Dim cell As Range
    Dim startingcell As String
    
    lrowS = shInput.cells(Rows.Count, 19).End(xlUp).Row
    
    For Each cell In shInput.Range("S13" & ":" & "S" & lrowS)
    
        startingcell = cell.Address
    
        If cell.Text = "" And IsEmpty(cell.Offset(-1, 0)) = True Then
    
        ElseIf cell.Text = "OK" Or cell.Text = "KO" And IsEmpty(cell.Offset(-1, 0)) = True Then
    
            MsgBox "Please remove the blank rows"
            Exit Function
       
        End If
    
    Next cell
    
End Function

Solution

Please, test the next adapted function. I assume that DefineVariables defines the shInput worksheet. My code, for testing reasons, defines the sheet in discussion as the active one. You can delete/comment the declaration and the value allocation:

Private Function detecht_empty_rows() As Boolean

'Call DefineVariables

Dim lrowS As Long, cell As Range, startingcell As String
Dim shInput As Worksheet, boolEmpty As Boolean, rowNo As Long

Set shInput = ActiveSheet 'use here your defined worksheet. 
                          'Clear the declaration if declared at the module level
lrowS = shInput.cells(rows.count, 19).End(xlUp).row
'new inserted code line:________________________________
lrowS = lastR(shInput.range("S13" & ":" & "S" & lrowS))
'_______________________________________________________

For Each cell In shInput.Range("S13" & ":" & "S" & lrowS)
    If cell.text = "" And cell.Offset(-1, 0) = "" Then
        boolEmpty = True: rowNo = cell.Offset(-1).row: Exit For
    ElseIf (cell.text = "OK" Or cell.text = "KO") And cell.Offset(-1, 0) = "" Then
        boolEmpty = True: rowNo = cell.Offset(-1).row: Exit For
    End If
 Next cell
 If boolEmpty Then MsgBox "Please remove the blank row (" & rowNo & ").": detecht_empty_rows = False: Exit Function

detect_empty_rows = True
End Function

The next function will calculate the last row to be processed in a different way:

Function lastR(rng As range) As Long
   Dim i As Long, lngStart As Long, lngEnd As Long, sh As Worksheet
   
   lngStart = rng.cells(1).Row: lngEnd = lngStart + rng.Rows.Count - 1
   Set sh = rng.Parent
   For i = lngStart To lngEnd
       If WorksheetFunction.CountIf(sh.range(sh.range("S" & i), sh.range("S" & lngEnd)), "OK") + _
            WorksheetFunction.CountIf(sh.range(sh.range("S" & i), sh.range("S" & lngEnd)), "KO") = 0 Then
            lastR = i - 1: Exit Function
       End If
   Next i
End Function

You must change

ElseIf cell.text = "OK" Or cell.text = "KO" And IsEmpty(cell.Offset(-1, 0)) 

with

ElseIf (cell.text = "OK" Or cell.text = "KO") And cell.Offset(-1, 0) = "" 

The Or conditions must be checked like a single check toghether with IsEmpty part.

Then, startingcell = cell.Address is useless and unused, it takes a new value for each iteration.

Not necessarily to use IsEmpty(cell.Offset(-1, 0)) = True. It is enough to use IsEmpty(cell.Offset(-1, 0)).The method returns a Boolean variable, anyhow.

Being a function returning a Boolean, it should return it. It can be used in the code calling the function.

But in case of a formula, even if it returns a null string (""), IsEmpty cannot be used. I mean, it does not work, the cell no being empty. The code must use cell.Offset(-1, 0) = "".

Please, take care to not have an empty cell at "S12"...

Answered By - FaneDuru

Answer Checked By - Terry (PHPFixing Volunteer)