Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check SpecialCells(xlCellTypeBlanks) for 0 blanks
How do I check whether the Range returned does have cells in it - otherwise
you would get an runtime error. Checking with Null, Empty, Error, etc. does not work. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check SpecialCells(xlCellTypeBlanks) for 0 blanks
dim myRng as range
set myrng = nothing on error resume next set myrng = somerange.cells.specialcells(xlcelltypeblanks) on error goto 0 if myrng is nothing then 'no cells found else msgbox myrng.cells.count end if Gerry Verschuuren wrote: How do I check whether the Range returned does have cells in it - otherwise you would get an runtime error. Checking with Null, Empty, Error, etc. does not work. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check SpecialCells(xlCellTypeBlanks) for 0 blanks
Thank you, but I was hoping I could do this without "On Error Resume Next"
but I guess that is not possible. "Dave Peterson" wrote: dim myRng as range set myrng = nothing on error resume next set myrng = somerange.cells.specialcells(xlcelltypeblanks) on error goto 0 if myrng is nothing then 'no cells found else msgbox myrng.cells.count end if Gerry Verschuuren wrote: How do I check whether the Range returned does have cells in it - otherwise you would get an runtime error. Checking with Null, Empty, Error, etc. does not work. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check SpecialCells(xlCellTypeBlanks) for 0 blanks
I would use the first suggestion, but maybe you could use:
if somerange.cells.count = application.counta(somerange) then 'everycell has something in it else 'at least one empty cell end if Gerry Verschuuren wrote: Thank you, but I was hoping I could do this without "On Error Resume Next" but I guess that is not possible. "Dave Peterson" wrote: dim myRng as range set myrng = nothing on error resume next set myrng = somerange.cells.specialcells(xlcelltypeblanks) on error goto 0 if myrng is nothing then 'no cells found else msgbox myrng.cells.count end if Gerry Verschuuren wrote: How do I check whether the Range returned does have cells in it - otherwise you would get an runtime error. Checking with Null, Empty, Error, etc. does not work. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check SpecialCells(xlCellTypeBlanks) for 0 blanks
That's a great one. Fantastic. I would have never thought of this solution.
Thanks a lot! "Dave Peterson" wrote: I would use the first suggestion, but maybe you could use: if somerange.cells.count = application.counta(somerange) then 'everycell has something in it else 'at least one empty cell end if Gerry Verschuuren wrote: Thank you, but I was hoping I could do this without "On Error Resume Next" but I guess that is not possible. "Dave Peterson" wrote: dim myRng as range set myrng = nothing on error resume next set myrng = somerange.cells.specialcells(xlcelltypeblanks) on error goto 0 if myrng is nothing then 'no cells found else msgbox myrng.cells.count end if Gerry Verschuuren wrote: How do I check whether the Range returned does have cells in it - otherwise you would get an runtime error. Checking with Null, Empty, Error, etc. does not work. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement to Check for Blanks | Excel Worksheet Functions | |||
Work around to SpecialCells(xlCellTypeBlanks)... | Excel Discussion (Misc queries) | |||
Need Check for Blanks formula, please | Excel Worksheet Functions | |||
Help to hide rows, SpecialCells(xlCellTypeBlanks) doesn't work... | Excel Programming | |||
specialcells(xlcelltypeblanks) | Excel Programming |