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.

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

' 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.

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

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