Validation for blank cells

[crims0n]

Hi,

Here is a code for checking blank cells in 1 column. Let's say we have 3 column populated with values. On the last row, column 1 is empty while the other are not. when i run this code it returns No Blanks. What do I need to add so it can also check the last row which is empty.

* I ommitted the part where it gives the number of blank cells since i'am currently working on the last blank row.

  Dim Holder As Range
  Dim Answer As Integer
  Dim blanks
 
  Set Holder = Range("A2:A" & Range("A65536").End(xlUp).Row)
  Answer = 0
 
' Count each blank cell.
       For Each x In Holder
           If IsEmpty(x.Value) Then Answer = Answer + 1
       Next x
 
   blanks = Holder.Address(False, False)
 
   If Answer > 0 Then
    MsgBox "There are blanks in " & blanks & ""   
   Else
    MsgBox "There are No blanks"
   End If

Thanks

 

[jbeaucaire]

How about this to check all three columns for blank cells?

Sub TestForBlanks()
Dim LR As Long
Dim RNG As Range

LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
On Error Resume Next
Set RNG = Range("A2:C" & LR).SpecialCells(xlCellTypeBlanks)

If Not RNG Is Nothing Then
    MsgBox "There are " & RNG.Cells.Count & " blank cells"
Else
    MsgBox "There are no blank cells"
End If

End Sub

You can change that red C to A to really just check column A.

 

[crims0n]

How about this to check all three columns for blank cells?

You can change that red C to A to really just check column A.

Wow, great. Thanks