I was looking for code to delete cell names within a selected range.
I found the following code which works just fine in the WB I created
to test it, but then won't work in the existing workbook for which I
needed it. It keeps giving a 1004 Run-time error and stops at this
line: " If WithinRange(myCell, myName.RefersToRange) Then".

I have no idea why it works ok in my test WB but not the other one. I
have ensured cells and worksheet are not locked.

Here is the full code - where "TESTRANGE" is the range that contains
the named cells I want to erase. Not all cells within the range are
named.

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 was thinking very hard :
I was looking for code to delete cell names within a selected range.
I found the following code which works just fine in the WB I created
to test it, but then won't work in the existing workbook for which I
needed it. It keeps giving a 1004 Run-time error and stops at this
line: " If WithinRange(myCell, myName.RefersToRange) Then".

I have no idea why it works ok in my test WB but not the other one. I
have ensured cells and worksheet are not locked.

Here is the full code - where "TESTRANGE" is the range that contains
the named cells I want to erase. Not all cells within the range are
named.

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

See my new reply to your other post...

--
Garry

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

On Apr 29, 12:52*am, GS wrote:
hdf was thinking very hard :









I was looking for code to delete cell names within a selected range.
I found the following code which works just fine in the WB I created
to test it, but then won't work in the existing workbook for which I
needed it. *It keeps giving a 1004 Run-time error and stops at this
line: " If WithinRange(myCell, myName.RefersToRange) Then".

I have no idea why it works ok in my test WB but not the other one. *I
have ensured cells and worksheet are not locked.

Here is the full code - where "TESTRANGE" is the range that contains
the named cells I want to erase. *Not all cells within the range are
named.

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

See my new reply to your other post...

--
Garry

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

Thanks Gary. Your suggestion also works, but I am having the same
problem with your version too - it works on a new workbook, but does
not work in my workbook. It gives a 1004 Run-time error. I provide
more details in the other post.

http://groups.google.com/group/micro...3b94a1c7250c10