Range existence check

[Guest]

I need to delete a range and re-create it later in my code. I tried the
following but it doesn't work if the range does not exist:

On Error Resume Next
Range("D_CARDS").Delete

How can I check for the existence of a range and, in case it does exist,
delete it, otherwise continue with my code?

 

[Guest]

I am not sure about the validity of your statement that it doesn't work, but
if you want to be more verbose:

Dim rng as Range
Set rng = Nothing
On error Resume Next
set rng = Range("D_Cards")
On error goto 0
if not rng is nothing then
rng.Delete
end if

 

[Bob Phillips]

If it doesn't exist, how do you know it didn't work?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Tom I get the same response from Excel as before :

Run-time error 1004
Method 'Range' of object '_Global' failed

Can you guess what might be wrong? I guess that's why you weren't sure about
the validity of my previous statement. Something else is probably causing
this behaviour.

 

[Guest]

I get the 1004 error message when Excel processes either of the following
statements:

Range("D_CARDS").Delete
or
set rng = RANGE("D_CARDS")

 

[Guest]

I pasted the code in a blank sheet/new workbook and ran it with no problem.
the Error handler will suppress a 1004 error in either my code or your
original. Wherever the error is coming from, it is not in the region covered
by
On Error Resume Next


I also don't know what you want to achieve by doing Range("D_Cards").Delete

when you do that, D_Cards continues to exist as a named range, but it has a
refers to argument like =Sheet1!#REf

If you want to clear the data in that range, use

Range("D_Cards").Clear
if you want to remove the name
Thisworkbook.Names("D_Cards").Delete

if you want to do both
Range("D_Cards").Clear
Thisworkbook.Names("D_Cards").Delete

 

[Guest]

You are right on the bad reference the Range("D_CARDS").Delete command
causes. Perhaps this is the reason the 1004 appears in the first place.

Anyway you covered all options in your answer, along with what I need to do,
so ... Thanks a lot!! Case closed.