IsNotEmpty Range

[Randy]

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 D1463). 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("D1463")) 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

 

[Rick Rothstein]

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("D1463"). _
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

 

[Gary''s Student]

Sub BlankDetector()
If Application.WorksheetFunction.CountBlank(Range("D1463")) > 0 Then
MsgBox "data misssing"
End If
End Sub

 

[Dave Peterson]

I'd try:

Private Sub Sr_Mgr_Click()

with workSheets("Sr-Area Manager").Range("D1463")
if .cells.count = application.counta(.cells) then
'all the cells have something in them
else
'not all the cells have something in them
end if
end with

End Sub

Remember that formulas that evaluate to ="" (looking empty) still count as
having something in them.

 

[Randy]

Awesome Gary! A little tweaking and worked like a charm. Thank you very much!
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 cn get this to work on rows 39-83 when they are visible only?

 

[Randy]

Thanks Dave....you are awesome as always! Now I have another question for
you....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

I'd try:

Private Sub Sr_Mgr_Click()

with workSheets("Sr-Area Manager").Range("D1463")
if .cells.count = application.counta(.cells) then
'all the cells have something in them
else
'not all the cells have something in them
end if
end with

End Sub

Remember that formulas that evaluate to ="" (looking empty) still count as
having something in them.

 

[Randy]

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?

 

[Dave Peterson]

Option Explicit
Private Sub Sr_Mgr_Click()

Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Worksheets("Sr-Area Manager").Range("D1463") _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If myRng Is Nothing Then
'all hidden--everything is ok (right?)
Else
With myRng
If .Cells.Count = Application.CountA(.Cells) Then
MsgBox "all the cells have something in them"
Else
MsgBox "not all the cells have something in them"
End If
End With
End If

End Sub

Thanks Dave....you are awesome as always! Now I have another question for
you....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]

You my friend are simply amazing! I cannot wait till I get to your level!
Thanks gain and have a wonderful holiday season!
--
Randy Street
Rancho Cucamonga, CA

Option Explicit
Private Sub Sr_Mgr_Click()

Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Worksheets("Sr-Area Manager").Range("D1463") _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If myRng Is Nothing Then
'all hidden--everything is ok (right?)
Else
With myRng
If .Cells.Count = Application.CountA(.Cells) Then
MsgBox "all the cells have something in them"
Else
MsgBox "not all the cells have something in them"
End If
End With
End If

End Sub

 

[Rick Rothstein]

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