Code to delete names works in one WB, but not another WB?

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

hdf

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
Click to expand...

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
 
hdf was thinking very hard :
















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
Click to expand...

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/micr...&lnk=gst&q=delete+cell+names#7a3b94a1c7250c10
 
Back
Top