CODEWITHSUNDEEP
vbaexcel
Jeofbist3
Jeofbist3

Reputation: 47

VBA - Check blank cells - Wrong output

I have a code that checks in a range if some cells are blank (empty or not). It gives me a message saying so. But, it seems not working well : the output message always says that there are some empty cells in the range (column A to H, until the last populated row) whereas it's the contrary (always data). I precise that the layout of the range is a table! MsgBox(LastRow) is every time equal to the last row also.. Here is a part of the code:

Set sht = ThisWorkbook.Worksheets("SS upload")         
Set StartCell = Range("A14")            
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
MsgBox (LastRow)
Set Rrng = Range("A14 : H" & LastRow)
For Each cell In Rrng
If IsEmpty(cell) = True Then
bIsEmpty = True
Exit For
End If
Next cell
If bIsEmpty = True Then
MsgBox "There are empty cells in the file"
Else
MsgBox "All cells have values!"
End If
End Sub

Does anything seem wrong in this?

Thank you for your precious help! :) Regards

Upvotes: 0

Views: 337

Answers (2)

Jeofbist3
Jeofbist3

Reputation: 47

That's strange indeed because it also work sometimes. I mean the output message "All cells have value" is conformed to what's really in the file (no blanks at all) but sometimes not..

Here is my full code: 

  Sub empty_cells()                 
  Dim sht As Worksheet        
  Dim Rrng As Range        
  Dim cell As Range                
  Dim LastRow As Long
  Dim StartCell As Range
  Dim bIsEmpty As Boolean
  Set sht = ThisWorkbook.Worksheets("SS upload")
  Set StartCell = Range("A14")
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  MsgBox (LastRow)
  Set Rrng = Range("A14 : H" & LastRow)
  For Each cell In Rrng
  If IsEmpty(cell) = True Then
  bIsEmpty = True
  Exit For
 End If
 Next cell
If bIsEmpty = True Then
MsgBox "There are empty cells in the file"
Else
MsgBox "All cells have values!"
  End If
 End Sub

thanks for your support :)

Upvotes: 0

Vityata
Vityata

Reputation: 43585

Probably you are not realizing, that you are looking in a range(A14:H LAST Row) Thus, if you have 5 rows, then the range is still Range(A14:H5). And there, you have empty values.

Public Sub TestME()

    Dim bIsEmpty As Boolean

    Set sht = ThisWorkbook.Worksheets(2)
    Set StartCell = Range("A14")

    LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).row

    MsgBox (LastRow)
    Set Rrng = Range("A1 : H" & LastRow)

    For Each cell In Rrng
        If IsEmpty(cell) = True Then
            bIsEmpty = True
            Exit For
        End If
    Next cell

    If bIsEmpty Then
        MsgBox "There are empty cells in the file"
    Else
        MsgBox "All cells have values!"
    End If

End Sub

Upvotes: 1

Related Questions