IsNotEmpty Range
 

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

IsNotEmpty Range
 

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

IsNotEmpty Range
 

Sub BlankDetector()
If Application.WorksheetFunction.CountBlank(Range("D1 4:D63")) 0 Then
MsgBox "data misssing"
End If
End Sub

--
Gary''s Student - gsnu200909


"Randy" wrote:

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

IsNotEmpty Range
 

I'd try:

Private Sub Sr_Mgr_Click()

with workSheets("Sr-Area Manager").Range("D14:D63")
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 wrote:

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

--

Dave Peterson

IsNotEmpty Range
 

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 Street
Rancho Cucamonga, CA


"Gary''s Student" wrote:

Sub BlankDetector()
If Application.WorksheetFunction.CountBlank(Range("D1 4:D63")) 0 Then
MsgBox "data misssing"
End If
End Sub

--
Gary''s Student - gsnu200909


"Randy" wrote:

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

IsNotEmpty Range
 

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


"Dave Peterson" wrote:

I'd try:

Private Sub Sr_Mgr_Click()

with workSheets("Sr-Area Manager").Range("D14:D63")
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 wrote:

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

--

Dave Peterson
.

IsNotEmpty Range
 

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

.

IsNotEmpty Range
 

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("D14:D63") _
.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

Randy wrote:

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

"Dave Peterson" wrote:

I'd try:

Private Sub Sr_Mgr_Click()

with workSheets("Sr-Area Manager").Range("D14:D63")
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 wrote:

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

--

Dave Peterson
.


--

Dave Peterson

IsNotEmpty Range
 

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


"Dave Peterson" wrote:

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("D14:D63") _
.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

Randy wrote:

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

"Dave Peterson" wrote:

I'd try:

Private Sub Sr_Mgr_Click()

with workSheets("Sr-Area Manager").Range("D14:D63")
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 wrote:

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

--

Dave Peterson
.


--

Dave Peterson
.

IsNotEmpty Range
 

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

.