Range existence check

[DoctorG]

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?

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"DoctorG" wrote:

> 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?

[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)

"DoctorG" <[email protected]> wrote in message
news:[email protected]...
> 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?

[DoctorG]

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.

"Tom Ogilvy" wrote:

> 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
>
> --
> Regards,
> Tom Ogilvy
>
> "DoctorG" wrote:
>
> > 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?

[DoctorG]

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

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

"Bob Phillips" wrote:

> 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)
>
> "DoctorG" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy


"DoctorG" wrote:

> 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.
>
> "Tom Ogilvy" wrote:
>
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "DoctorG" wrote:
> >
> > > 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?

[DoctorG]

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.