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

[Excelenator]

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:

Please Login or Register  to view this content.

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]

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

Please Login or Register  to view this content.

and it works like a charm!!!

[JMB]

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
>
>

[Excelenator]

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!

[NickHK]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>