This should do it for you...

Dim L As Long
......
......
On Error Resume Next
L = Sheets("Sr-Area Manager").Range("A1:E9").SpecialCells( _
xlCellTypeVisible).SpecialCells(xlCellTypeBlanks). Count
On Error GoTo 0
If L 0 Then
'
' There is at least one empty cell in
' the range of data so run your code here
'
End If

--
Rick (MVP - Excel)


"Randy" wrote in message
...
Thank you Rick! Now I have another question for ya....rows 14-38 are
visible....rows 39-83
are hidden unless the user clicks a botton that makes rows 39-83
visible...is
there a way I can get this to work on rows 39-83 when they are visible
only?

--
Randy Street
Rancho Cucamonga, CA


"Rick Rothstein" wrote:

Assuming the cells in the range contain data and not formulas, give this
coding idea a try...

On Error Resume Next
If Sheets("Sr-Area Manager").Range("D14:D63"). _
SpecialCells(xlCellTypeBlanks).Count Then
'
' There is at least one empty cell in
' the range of data so run your code here
'
End If
On Error GoTo 0

--
Rick (MVP - Excel)


"Randy" wrote in message
...
Hello...I have a little vb code tht I am trying to get to work with a
range
of cells. Basically if any of the cells in the range are empty it
should
return a message. the code works if a single cell (such as D14) is used
but
not when I put the range (such as D14:D63). Anyone have any ideas as to
how I
can get this to work without listing each cell individually?

here is what I hve so far:

Private Sub Sr_Mgr_Click()
If IsEmpty(Sheets("Sr-Area Manager").Range("D14:D63")) Then
r = MsgBox("You are missing a Move-In Date. Move-In Dates
are a
required field. Please verify you have ALL Move-In Dates entered before
continuing!", _
vbQuestion + vbOKOnly, "Error?")
If r = vbOK Then
Range("D14").Select
End
End If

End If
End Sub
--
Randy Street
Rancho Cucamonga, CA

.