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.

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

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
>

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

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
>