Check SpecialCells(xlCellTypeBlanks) for >0 blanks

[Guest]

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]

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

 

[Guest]

Thank you, but I was hoping I could do this without "On Error Resume Next"
but I guess that is not possible.

 

[Dave Peterson]

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

 

[Guest]

That's a great one. Fantastic. I would have never thought of this solution.
Thanks a lot!