Finding Blank Cells in a Dynamic Range: VBA

[nadineXCEL]

The following code of mine is not running:


Sub Blank_Cell_Alert()


Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "MyRange"
ActiveSheet.Name = "MySheet"


If IsEmpty(ThisWorkbook.Sheets("MySheet").Range("MyRange")) Then
MsgBox ("Please Enter all Employee Data Before you Attempt to Update this Table."), vbCritical, "Incomplete Data!"
Cancel = True

End If
End Sub


______



I recorded a macro of myself selecting a range. I need to look up from this data any cell that is blank, and should there be a cell that is blank i.e. have missing information, I need an error message to pop up.
Thanks in advance.

 

[JLGWhiz]

See if this works for you

Sub Blank_Cell_Alert()
 Range("A2").Select
 Range(Selection, Selection.End(xlToRight)).Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.Name = "MyRange"
 ActiveSheet.Name = "MySheet"
 If Range("MyRange").SpecialCells(xlCellTypeBlanks).Count > 0 Then
     MsgBox ("Please Enter all Employee Data Before you Attempt to Update this Table."), vbCritical, "Incomplete Data!" 
     Cancel = True
 End If
 End Sub