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.

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

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

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

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