Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range existence check
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range existence check
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range existence check
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range existence check
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range existence check
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range existence check
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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range existence check
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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for existence | Excel Discussion (Misc queries) | |||
VBA to check for existence of a DSN | Excel Programming | |||
Check File Existence | Excel Programming | |||
How do I check for existence of a worksheet? | Excel Worksheet Functions | |||
Code to check existence | Excel Programming |