Delete range names within a selection

  • Thread starter Thread starter hdf
  • Start date Start date
H

hdf

I've been searching extensively and can't seem to find something that
works, so I will try here.

I need to develop vba code that will delete all cell names within a
selected range. The selected range will include cells that are named
and cells that are not named.

Any help will be greatly appreciated.
 
hdf presented the following explanation :
I've been searching extensively and can't seem to find something that
works, so I will try here.

I need to develop vba code that will delete all cell names within a
selected range. The selected range will include cells that are named
and cells that are not named.

Any help will be greatly appreciated.
Click to expand...

Have a look at the Intersect() function in VBA help.

Example...
<aircode>
Dim rngname As Variant
' For Each rngname In ActiveWorkbook.Names '//use for global scope
For Each rngname In ActiveSheet.Names '//use for local scope
If Not Intersect(Selection, Range(rngname)) Is Nothing Then
'..delete the name
' ActiveWorkbook.Names(rngname).Delete '//use for global scope
ActiveSheet.Names(rngname).Delete '//use for local scope
End If
Next 'rngname

...where this will work if all or part of a named range falls within the
selection.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Oops! See the corrections below...
Example...
<aircode>
Dim rngname As Variant
' For Each rngname In ActiveWorkbook.Names '//use for global scope
For Each rngname In ActiveSheet.Names '//use for local scope
If Not Intersect(Selection, Range(rngname)) Is Nothing Then
'..delete the name
Click to expand...

' ActiveWorkbook.Names(rngname.Name).Delete '//for global scope
ActiveSheet.Names(rngname.Name).Delete '//for local scope
End If
Next 'rngname

..where this will work if all or part of a named range falls within the
selection.
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Garry,

Thanks for the help. I was trying your first version and couldn't get
it to work, thanks for correcting it. I will now try and work with the
revised version.

HDF
 
Well, I have not been able to get Garry's recommendations to work. I
am clearly not setting it up with all of the correct parameters.

However, I did find some code in another thread and now I have the
problem that it works just fine when testing it on a newly created
workbook, but for the life of me I can't get the exact same code to
work in my existing workbook.

I keep getting a 1004 Run-time error and it stops at the following
line of code: " If WithinRange(myCell, myName.RefersToRange) Then"

Here is the code:

Sub DeleteNames()

Dim myRange As Range
Dim myCell As Range
Dim myName As Name

Set myRange = Range("TESTRANGE")
For Each myCell In myRange.Cells
For Each myName In ThisWorkbook.Names
If WithinRange(myCell, myName.RefersToRange) Then
myName.Delete
End If
Next myName
Next myCell

End Sub
-------
Function WithinRange(SmallRng, BigRng) As Boolean
' Returns True if smallrng is a subset of Bigrng
WithinRange = False
If SmallRng.Parent.Name = BigRng.Parent.Name Then
If Union(SmallRng, BigRng).Address = BigRng.Address Then
WithinRange = True
End If
End If
End Function
 
hdf used his keyboard to write :
Well, I have not been able to get Garry's recommendations to work. I
am clearly not setting it up with all of the correct parameters
Click to expand...

All the correct parameters are there. I tested this with both local
scope named ranges and global scope named ranges. It worked perfectly
for me and so the only reason it may not be working for you is because
you're running it as posted, but your named ranges are global scope NOT
local scope. In this case, comment out the lines that act on
ActiveSheet and uncomment the lines that act on ActiveWorkbook.
(Commented lines are the ones prefixed with an apostrophe. Remove the
apostrophe from the global scope line and prefix the the local scope
line with an apostrophe!)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Note that there are 2 lines each for global/local scope names. You must
change both as shown here...

Dim rngname As Variant
For Each rngname In ActiveWorkbook.Names '//use for global scope
' For Each rngname In ActiveSheet.Names '//use for local scope
If Not Intersect(Selection, Range(rngname)) Is Nothing Then
'..delete the name
ActiveWorkbook.Names(rngname.Name).Delete '//for global scope
' ActiveSheet.Names(rngname.Name).Delete '//for local scope
End If
Next 'rngname

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
GS expressed precisely :
Note that there are 2 lines each for global/local scope names. You must
change both as shown here...
Sub DeleteIntersectinNames()
Dim rngname As Variant
For Each rngname In ActiveWorkbook.Names '//use for global scope
' For Each rngname In ActiveSheet.Names '//use for local scope
If Not Intersect(Selection, Range(rngname)) Is Nothing Then
'..delete the name
ActiveWorkbook.Names(rngname.Name).Delete '//for global scope
' ActiveSheet.Names(rngname.Name).Delete '//for local scope
End If
Next 'rngname
Click to expand...
End Sub

It just occurred to me that you may not have put the code inside a
procedure and so the above has been edited to demonstrate how to
implement it.

(If your range names are indeed local scope then switch the apostophes
around to the original post)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Gary,

Thanks for your inputs. Indeed I was over thinking and was reading
into your code more than I should have. Your code works just fine when
I test it on a new workbook. However, I am having the same problem
with your code as I do with the much more complex code I mentioned
above - it will not work in the workbook for which I actually need it.

I gives me a 1004 run time error if I set it to run at the
ActiveWorkbook.Names level and if I set it at the ActiveSheet.Names
level it doesn't do anything. It runs through the code, does not
throw off any errors, but also does not erase any names within the
selected range. Neither the WB or WS are protected.

Any ideas on what might be causing this behaviour?

Thanks for your help and patience.

Hector
 
I should provide some additional information on my problem.

I realize now that the Worksheet level version doesn't do anything
because the names I am trying to erase are Workbook level.

When running the workbook version I get a run-time error. The error
is a 1004 run-time error that says, specifically, "Method 'Range' of
object '_Global' failed."

When I press the "Debug" option it highlights the following part of
Gary's code:

If Not Intersect(Selection, Range(rngname)) Is Nothing Then

If I use the exact same code on a new workbook created to test it, it
works as intended and erases the range names located within the
selected area.

I have ensured none of the worksheets nor the workbook are locked in
any way. I have also unlocked all cells to see if that would do the
trick, no luck either. I don't know what else to try.
 
I should provide some additional information on my problem.

I realize now that the Worksheet level version doesn't do anything
because the names I am trying to erase are Workbook level.

When running the workbook version I get a run-time error. The error
is a 1004 run-time error that says, specifically, "Method 'Range' of
object '_Global' failed."

When I press the "Debug" option it highlights the following part of
Gary's code:

If Not Intersect(Selection, Range(rngname)) Is Nothing Then

If I use the exact same code on a new workbook created to test it, it
works as intended and erases the range names located within the
selected area.

I have ensured none of the worksheets nor the workbook are locked in
any way. I have also unlocked all cells to see if that would do the
trick, no luck either. I don't know what else to try.
Click to expand...

For the code to work in any workbook, it must be stored in a standard
module and run from the Excel window via the Macros dialog. Optionally,
you can run it from the VBE -OR- store it in a standard module in
PERSONAL.XLS. I suspect that you're trying to run the code behind a
sheet module and so when you select that it activates that workbook's
worksheet.

The code will do nothing if no ranges have defined names, or the
defined names are outside the scope for the line of code running.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
For the code to work in any workbook, it must be stored in a standard
module and run from the Excel window via the Macros dialog. Optionally,
you can run it from the VBE -OR- store it in a standard module in
PERSONAL.XLS. I suspect that you're trying to run the code behind a
sheet module and so when you select that it activates that workbook's
worksheet.

The code will do nothing if no ranges have definednames, or the
definednamesare outside the scope for the line of code running.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
Click to expand...

Thanks for the follow up.

The code is in a standard module. I have discovered that if I use the
code on a workbook with more than one WS and both worksheets have
names in them, it crashes.

I started with a fresh WB, named four consecutive cells in each of two
WS (different names for each cell). Then I placed a button linked to
the macro in each WS. I then select half of the named cells in either
one of the two WS and run the macro. It crashes.

If I start with a fresh WB and only name cells on one WS, select some
of them and run the macro, it works as intended.

Is there any reason why it would only work on a single WS workbook?
 
hdf wrote on 4/29/2012 :
Thanks for the follow up.

The code is in a standard module. I have discovered that if I use the
code on a workbook with more than one WS and both worksheets have
names in them, it crashes.

I started with a fresh WB, named four consecutive cells in each of two
WS (different names for each cell). Then I placed a button linked to
the macro in each WS. I then select half of the named cells in either
one of the two WS and run the macro. It crashes.

If I start with a fresh WB and only name cells on one WS, select some
of them and run the macro, it works as intended.

Is there any reason why it would only work on a single WS workbook?
Click to expand...

It works for the active sheet of whichever workbook is active at the
time the code runs. It doesn't matter how many sheets are in the
workbook because only 1 sheet can be active at any time.

What's important to make it work is whether the names are defined with
local or global scope. To make this easier for you, I've created 2
separate procedures so you can choose the scope befor you run the
code...

Sub DeleteGlobalScopeNames()
Dim rngname As Variant
For Each rngname In ActiveWorkbook.Names
If Not Intersect(Selection, Range(rngname)) Is Nothing Then
'..delete the name
ActiveWorkbook.Names(rngname.Name).Delete
End If
Next 'rngname
End Sub

Sub DeleteLocalScopeNames()
Dim rngname As Variant
For Each rngname In ActiveSheet.Names
If Not Intersect(Selection, Range(rngname)) Is Nothing Then
'..delete the name
ActiveSheet.Names(rngname.Name).Delete
End If
Next 'rngname
End Sub

I suggest you put this in PERSONAL.XLS so it's always available
whenever you have Excel open. If your PERSONAL.XLS file is not open (in
the VBE Explorer pane) then it doesn't exist yet. You can create one by
recording a macro and choosing PERSONAL.XLS during the process. You
don't actually have to record anything so just click 'Stop recording',
delete the empty macro in 'Module1', then paste these subs into that
module.

To run the macros from the Excel UI, open the 'Macros...' dialog:

In Ribbon versions of Excel it can be found on the 'Developer' tab.

In earlier versions of Excel it can be accessed via the 'Tools' menu.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top