Test if the range is empty
 

I need a VBA code to ensure the range to be filled, is empty to begin.
I will select the range, test if the range is empty, then proceed
accordingly.
Thanks

Test if the range is empty
 

Application.CountA(rngToBeFilled)
(you don't need to select the range)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"dan"
wrote in message
I need a VBA code to ensure the range to be filled, is empty to begin.
I will select the range, test if the range is empty, then proceed
accordingly.
Thanks

Test if the range is empty
 

Thank you, Jim
Nice trick.

"Jim Cone" wrote in message
...

Application.CountA(rngToBeFilled)
(you don't need to select the range)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"dan"
wrote in message
I need a VBA code to ensure the range to be filled, is empty to begin.
I will select the range, test if the range is empty, then proceed
accordingly.
Thanks

Test if the range is empty
 

Jim - I opened a new workbook - made no entries into sheet1 (the
activesheet)..

From the immediate Window I entered:
set rng = Activesheet.Range("G1:G5")
? rng.address
$G$1:$G$5
? isempty(rng)
FALSE << WHY???

TIA,
Jim


"Jim Cone" wrote in message
:

Application.CountA(rngToBeFilled)
(you don't need to select the range)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"dan"
wrote in message
I need a VBA code to ensure the range to be filled, is empty to begin.
I will select the range, test if the range is empty, then proceed
accordingly.
Thanks

Test if the range is empty
 

I would say you are asking it to do something beyond its
design specs. I can't give you the technical explanation,
but the help file says IsEmpty only works on Variant variables.
The fact that it works on single cells is just a nice bonus.
For what it's worth, I usually use Len to check a single cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"JMay"
wrote in message
Jim - I opened a new workbook - made no entries into sheet1 (the
activesheet)..

From the immediate Window I entered:
set rng = Activesheet.Range("G1:G5")
? rng.address
$G$1:$G$5
? isempty(rng)
FALSE << WHY???
TIA,
Jim


"Jim Cone"
wrote in message

Application.CountA(rngToBeFilled)
(you don't need to select the range)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"dan"
wrote in message
I need a VBA code to ensure the range to be filled, is empty to begin.
I will select the range, test if the range is empty, then proceed
accordingly.
Thanks

Test if the range is empty
 

Thanks for your help.
Jim also

"Jim Cone" wrote in message
:

I would say you are asking it to do something beyond its
design specs. I can't give you the technical explanation,
but the help file says IsEmpty only works on Variant variables.
The fact that it works on single cells is just a nice bonus.
For what it's worth, I usually use Len to check a single cell.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"JMay"
wrote in message
Jim - I opened a new workbook - made no entries into sheet1 (the
activesheet)..

From the immediate Window I entered:
set rng = Activesheet.Range("G1:G5")
? rng.address
$G$1:$G$5
? isempty(rng)
FALSE << WHY???
TIA,
Jim


"Jim Cone"
wrote in message

Application.CountA(rngToBeFilled)
(you don't need to select the range)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"dan"
wrote in message
I need a VBA code to ensure the range to be filled, is empty to begin.
I will select the range, test if the range is empty, then proceed
accordingly.
Thanks

Test if the range is empty
 

isempty(range("a1").value) will work on a single cell.

That's why Jim used application.counta() for multiple cells.

JMay wrote:

Jim - I opened a new workbook - made no entries into sheet1 (the
activesheet)..

From the immediate Window I entered:
set rng = Activesheet.Range("G1:G5")
? rng.address
$G$1:$G$5
? isempty(rng)
FALSE << WHY???

TIA,
Jim

"Jim Cone" wrote in message
:

Application.CountA(rngToBeFilled)
(you don't need to select the range)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"dan"
wrote in message
I need a VBA code to ensure the range to be filled, is empty to begin.
I will select the range, test if the range is empty, then proceed
accordingly.
Thanks

--

Dave Peterson