Determine if range has NO Blank Cells without looping through each cell in range
 

I have a range called "PreData" that is 12 rows and 6 columns and I want
to make sure the users fill in data in each of the cells. I am checking
the range in the Workbook_BeforeSave event and the code I am using to do
this is:


Code:
--------------------
Application.Goto Reference:="PreData"
Selection.SpecialCells(xlCellTypeBlanks).Select
--------------------


This works fine EXCEPT when there are no blanks. I know that the
Select is failing, I just don't know how to check it before actually
selecting it.

Any assistance will be appreciated!

Thanks!


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567685

Determine if range has NO Blank Cells without looping through each cell in range
 

Ok, I guess I have to answer my own question. The solution I worked out
is this


Code:
--------------------
Sub PreDataChk()
Dim blnks As Range
Dim isect As Range

Cells.Select
Selection.SpecialCells(xlCellTypeBlanks).Select

Set blnks = Selection
Set isect = Application.Intersect(Range("PreData"), blnks)

If Not isect Is Nothing Then
isect.Select
End If
End Sub
--------------------


and it works like a charm!!!


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567685

Determine if range has NO Blank Cells without looping through each
 

Maybe one of these approaches will help.

Sub test()
Dim rngTemp As Range

On Error Resume Next
Set rngTemp = Range("PreData").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rngTemp Is Nothing Then
MsgBox "No Blanks"
Else: rngTemp.Select
End If

End Sub


Sub test2()

With Range("PreData")
If Application.CountA(Range("PreData")) = _
.Cells.Count Then
MsgBox "No Blanks"
Else: .SpecialCells(xlCellTypeBlanks).Select
End If
End With

End Sub



"Excelenator" wrote:


I have a range called "PreData" that is 12 rows and 6 columns and I want
to make sure the users fill in data in each of the cells. I am checking
the range in the Workbook_BeforeSave event and the code I am using to do
this is:


Code:
--------------------
Application.Goto Reference:="PreData"
Selection.SpecialCells(xlCellTypeBlanks).Select
--------------------


This works fine EXCEPT when there are no blanks. I know that the
Select is failing, I just don't know how to check it before actually
selecting it.

Any assistance will be appreciated!

Thanks!


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567685

Determine if range has NO Blank Cells without looping through each cell in range
 

JMB,

Thanks for your reply. I'm intrigued by the "On error resume next
example. I see that the code that WAS throwing the error will now NO
throw an error but will execute the next line of code. BRILLIANT!!
see you inserted "On error Goto 0" after that. Is that simply negatin
the "On error resume next" statement so that any other errors will no
throw an exception or should I insert a lable 0 (zero) in the code t
"catch" the exceptions?

Thanks again

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56768

Determine if range has NO Blank Cells without looping through each cell in range
 

Yes, you are quite correct with the error handling mechanism, but "On Error
Goto 0" does not need a label.
Read about "On Error Statement" in the VBA Help.

NickHK

"Excelenator"
wrote in message
...

JMB,

Thanks for your reply. I'm intrigued by the "On error resume next"
example. I see that the code that WAS throwing the error will now NOT
throw an error but will execute the next line of code. BRILLIANT!! I
see you inserted "On error Goto 0" after that. Is that simply negating
the "On error resume next" statement so that any other errors will now
throw an exception or should I insert a lable 0 (zero) in the code to
"catch" the exceptions?

Thanks again!


--
Excelenator


------------------------------------------------------------------------
Excelenator's Profile:
http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567685