Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
determine if cell address is within a range of cells | Excel Programming | |||
Looping through a range of cells | Excel Programming | |||
Looping thru a range - storing IF not blank? | Excel Programming | |||
Looping through a range of cells | Excel Programming | |||
I need to determine a cell range based on a blank cell | Excel Programming |